CODEWITHSUNDEEP
mysql
black
black

Reputation: 745

fetching all the salary from the 2nd highest with limit

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

Upvotes: 0

Views: 191

Answers (4)

Utsav Kesharwani
Utsav Kesharwani

Reputation: 1745

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)

Upvotes: 0

black
black

Reputation: 745

its working fine with 

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

but how to use limit only?

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

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.

Upvotes: 1

Haji
Haji

Reputation: 2077

You can simply use query as 

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

Upvotes: 1

Related Questions