how to find n'th highest value of a column in mysql

Question

i want to know the n'th highest value from a column of a table for eg: 2nd highest salary from tbl_salary or 4th highest salary from tbl_salary

i seen somewhere below query

select salary from tbl_salary t
where &n = (select count(salary) 
from (select distinct salary from tbl_salary)
where t.salary<=salary);

if this is correct then please tell me how it works?

or is there any other simple way to get result?

Answer 1

You can do it using the limit clause:

select * from tbl_salary order by salary desc limit 2,1;

Answer 2

I'm sure there is a better way to do this, but:

SELECT salary FROM tbl_salary ORDER BY salary DESC LIMIT n,1

Where n is the position you want - 1 (i.e. to get the second highest salary it would be LIMIT 1,1)

Answer 3

If it's a basic query, then just use LIMIT:

-- get the 4th highest salary
SELECT salary FROM tbl_salary
ORDER BY salary DESC
LIMIT 4,1