0

i found using order by with limit to fetch 2nd highest salary from database.may be it can enhance database performance when fetching from large number, rather using sub-query.i want to know how to fetch all the salary starting from 2nd highest salary. i have created table like & db name = one

id                   salary
1                    50
2                    20
3                    70
4                    40
5                    85

now to find only the 2nd highest salary

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

to find the salary of 2nd and 3rd and 4th

select salary from one order by salary desc limit 2,1

how to fetch all the salary starting from 2nd highest without knowing the 2nd limit value.

thanks

black
  • 713
  • 4
  • 12
  • 27

4 Answers4

1

Just use a really, really big value:

select salary
from one
order by salary desc
limit 99999999 offset 1;

Alternatively, you could calculate it:

select salary
from one
where salary < (select min(salary) from one)

Both these will run faster with an index on one(salary).

And, there is a subtle difference between the two. The first will return the second salary, even if equal to the highest salary. The second version will return the second highest salary, regardless of duplicates.

Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
  • MySQL returned an empty result set (i.e. zero rows) :( – black Dec 28 '13 at 16:06
  • @black . . . Because I always seem to get the arguments backwards when using offset. Arrgh. I just made it explicit using the keyword `offset` rather than the implicitly with the comma. – Gordon Linoff Dec 28 '13 at 16:13
1

You can simply use query as 

select salary from one
where salary > (select min(salary) from one)
Haji
  • 1,819
  • 1
  • 13
  • 19
0

its working fine with 

select salary from one
where salary < (select max(salary) from one)

but how to use limit only?

black
  • 713
  • 4
  • 12
  • 27
0

Use the offset feature of mysql:

Since you want to select salaries from 2nd highest to the end, you can use following query:

select salary from one order by salary desc limit 18446744073709551615 offset 1

PS: 18446744073709551615 = 2^64-1 (Refer to Mysql Offset Infinite rows)

Community
  • 1
  • 1
Utsav Kesharwani
  • 1,595
  • 10
  • 21