Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I want to get the second highest salary or say nth highest salary.....pls help?
also how to get record of first n highest salaries?
Posted 8-Aug-11 22:16pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try this.
Select * from (select  ROW_NUMBER() OVER (ORDER BY Salary DESC) as ID, * from table) as t where ID = n
n - position of salary
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

If you can have duplicates (the same salary more than one time), one way is:
select c.*
from (select b.onetime, 
             row_number() over (order by b.onetime desc) position
      from (select distinct 
                   a.salary as onetime 
            from   sometable a) b) c
where c.position = 2
 
if you want yo get for example top 5 salaries then change the condition:
...where c.position <= 5
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Here you go. Other Alternates
 
Second highest value[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Try
select min(Salary) from TableName where Salary in 
    (select TOP 2 (Salary) from Payroll order by Salary desc)
  Permalink  
Comments
Dara Dhillon at 9-Aug-11 8:27am
   
►►this one is really works the best....super like◄◄
Dara Dhillon at 9-Aug-11 8:36am
   
there is limitation i found i.e if two employees have same maximum salary then the command will sort min from top 2 salaries ordered by desc which will give the result same as that of highest salary..
saj_21 at 28-Jan-13 4:03am
   
select min(Salary) from TableName where Salary in
(select DISTINCT TOP 2 (Salary) from Payroll order by Salary desc)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

To get second highest salary,
select max(Salary) from Employee where Salary not in(select max(Salary) from Employee)
  Permalink  
Comments
Dara Dhillon at 9-Aug-11 8:36am
   
........ perfect one .......
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

SELECT *
FROM emp e
WHERE (2) = (
SELECT COUNT( DISTINCT ( e2.sal ) )
FROM emp e2
WHERE e2.sal >= e.sal
);
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 8

Select max(sal) from emp where sal<(Select max(sal) from emp);
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 365
1 Sergey Alexandrovich Kryukov 319
2 CPallini 275
3 DamithSL 214
4 Maciej Los 185
0 OriginalGriff 5,455
1 DamithSL 4,457
2 Maciej Los 3,885
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,115


Advertise | Privacy | Mobile
Web04 | 2.8.141216.1 | Last Updated 16 Sep 2014
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100