Question

How we can get nth max salary from Employee Table (EMP_ID, Salary)

Emp_id salary
1 1000
3 2000
4 16000
2 10000
7 19000
8 12000

e.g. for 3rd max salary, salary is 12000 so answer will be emp_id : 8

Answer 1

Change 3 to n as per your need -

SELECT emp_id, MIN(salary) from (
SELECT emp_id, salary from Employee ORDER BY salary DESC LIMIT 3
);

Comments

We cant get emp_id while we are using aggregate function on salary.
Getting bellow error -
You tried to execute a query that does not include the specified expression 'emp_id ' as part of an aggregate function

---

Try MIN(salary) as min_salary in place of just MIN(salary) and let me know the result.

---

Same error.
I think inner sql will give 3 records and then we are using min() on salary for getting minimum salary but then DB will not be able to decide which emp_id should be returned out of 3 records.

---

let me test, give me few min

---

I tested the following code
BEGIN TRANSACTION;

CREATE TABLE Employee (emp_id integer PRIMARY KEY, salary Integer   );

INSERT INTO Employee VALUES(1,100);
INSERT INTO Employee VALUES(2,200);
INSERT INTO Employee VALUES(3,300);
INSERT INTO Employee VALUES(4,400);
INSERT INTO Employee VALUES(5,500);
COMMIT;

SELECT emp_id, MIN(salary) from (
SELECT emp_id, salary from Employee ORDER BY salary DESC LIMIT 3
);

Output
3|300


Tested on following link just select the above code and paste. http://www.tutorialspoint.com/execute_sql_online.php

---

Thanks Sir,
I was using w3school editor i think they use different DB.
I read somewhere that in some DB this will work, in some DB will not.

---

ur welcome,
Yes different DB may have some minor differences :)