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?

See also: http://stackoverflow.com/questions/2255964/obtain-value-preceding-maximum-value — Mark Elliot, Feb 24 '10 at 06:40

score 15 · Accepted Answer · edited Jan 11 '15 at 15:31

15

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

edited Jan 11 '15 at 15:31

rekinyz

6,576
2
29
32

answered Feb 24 '10 at 06:32

nickf

537,072
198
649
721

and if if it is not basic query then? – xkeshav Feb 24 '10 at 06:36
well if it's not a derived field or anything (eg: I want to find the user record of the person who has posted the 5th most articles) this method will work fine. After that, it's probably just a case of using a subquery. – nickf Feb 24 '10 at 13:11
3

@nickf, I'm getting 5th highest by executing the above query! `limit 3,1` will get 4th highest, am I right? Please advise – arun May 04 '14 at 05:17
@arun, `limit 0,1` is first result. So yes `limit 3,1` is 4th. – Pacerier Apr 14 '15 at 07:44

score 8 · Answer 2 · edited Aug 29 '14 at 11:43

8

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)

edited Aug 29 '14 at 11:43

Pradeep S

165
2
10

answered Sep 06 '12 at 06:57

Nikhil Navlakha

81
1
1

Thanks for your effort, but there's no point in answering a 2.5 year old question which has also an accepted answer. – fancyPants Sep 24 '12 at 12:30
1

@tombom syntax explanation – Dec 12 '12 at 23:03

score 6 · Answer 3 · edited May 03 '12 at 15:38

6

// 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....

edited May 03 '12 at 15:38

Wiseguy

20,522
8
65
81

answered May 03 '12 at 10:18

pardeep sharma

61
1
1

Swapnil T · Answer 4 · 2013-06-28T13:17:16.657

4

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

edited Jun 28 '13 at 13:17

answered Feb 01 '13 at 08:27

Swapnil T

557
7
17

score 2 · Answer 5 · answered Jan 29 '13 at 10:26

2

SELECT sal from emp order by sal desc limit 1,1

answered Jan 29 '13 at 10:26

imran ansari

685
5
2

score 2 · Answer 6 · answered Feb 24 '10 at 06:32

2

You can do it using the limit clause:

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

answered Feb 24 '10 at 06:32

road242

2,492
22
30

score 2 · Answer 7 · answered Feb 24 '10 at 06:32

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)

answered Feb 24 '10 at 06:32

jasonbar

13,333
4
38
46

by using keyword `DISTINCT` will give accurate answer...m i right? – xkeshav Feb 25 '10 at 18:11
@I Like PHP: If your table contains duplicate salaries, yes. Or you could use GROUP BY salary – jasonbar Feb 25 '10 at 18:27

score 0 · Answer 8 · answered Dec 05 '16 at 15:34

0

Simplest Implementation,

 (select * from tbl_salary order by salary desc limit 5) order by salary limit 1;


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

answered Dec 05 '16 at 15:34

Shankar Thiyagaraajan

1,705
4
28
46

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

8 Answers8

Linked