top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

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

+1 vote
859 views

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

posted Feb 1, 2015 by Garima Gupta

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

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
);
answer Feb 1, 2015 by Salil Agrawal
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 :)
...