Find second highest highest query doesn't work

1432 views mysql
7

I could not figure out why my query does not work. The errors shows aggregate function have to use group by. I add "group by MAX(Salary)" which can not do the trick. Please help, thanks!

Table:

enter image description here

Query

SELECT Name, MAX(Salary)
FROM  PRACTICE.SALARY
WHERE Salary 
NOT IN (SELECT MAX(Salary) FROM  PRACTICE.SALARY);

answered question

1 Answer

3

When you use aggregate function you need to add non-aggregate columns in group by

SELECT Name, MAX(Salary)
FROM  PRACTICE.SALARY
WHERE Salary 
NOT IN (SELECT MAX(Salary) FROM  PRACTICE.SALARY)
GROUP BY Name

but If you only want to get second heigh SALARY, I think you can use this query to get your expect result

Schema (MySQL v5.7)

CREATE TABLE T(
  Name VARCHAR(50),
  Salary int
);

INSERT INTO T VALUES ('Lucy',2008);
INSERT INTO T VALUES ('PETER',3000);
INSERT INTO T VALUES ('K',1900);
INSERT INTO T VALUES ('TOM',2015);
INSERT INTO T VALUES ('MARK',3001);

Query #1

SELECT Name, Salary
FROM  PRACTICE.SALARY
WHERE Salary <> (SELECT MAX(Salary) FROM PRACTICE.SALARY)
ORDER BY Salary DESC 
LIMIT 1;

| Name  | Salary |
| ----- | ------ |
| PETER | 3000   |

View on DB Fiddle

posted this

Have an answer?

JD

Please login first before posting an answer.