How to find n'th highest value of a column?

Question

Is there a command akin to:

• 2nd highest salary from tbl_salary or

• 4th highest salary from tbl_salary ?

I've seen:

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

How does this it works?

Are there other simple ways to get result?

Answer 1

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 3,1

Answer 2

select * from employee order by salary desc limit 1,1

Description : limit x,y

• x: The row offset from which you want to start displaying records. For nth record it will be n-1.
• y: The number of records you want to display. (Always 1 in this case)

Answer 3

Here is a very simple way to get the result of n'th highest value

put n=2 to get second highest salary
pur n=4 to get fourth highest salary
and so on...

Here is query
if n=2

select salary from tbl_salary e1
where 2 = (
select distinct(count(salary))
from tbl_salary e2
where e1.salary< e2.salary
)

Best luck

Answer 4

You can do it using the limit clause:

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

Answer 5

// for highest salary of table

select salary from table order by salary desc limit 0,1

// for second highest salary

select salary from table order by salary desc limit 1,1

Using this query you get nth number of salary from table....

Answer 6

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)