Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
See more:
I have one table
that having some values

id            name            salary
--------------------------------------
100           saju            30000
200            shibu            44409
300            mnau            36779
400            vinu            65679
900            sunil            56565
489            prabu            87768
345            anil            76467
213            vibin            75676
132            ramu            34546
---------------------------------------------------------------------------------
from this table i want to select third highest salary
how to write the queries for this
Posted

Try this query:-
SQL
select max(salary) from Employeedetails
where salary not in (select top 2 salary from Employeedetails order by salary desc )
 
Share this answer
 
Try the below query. For rank column specify the top n salart you want to find

SQL
WITH CTE
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY RATE DESC)[Rank],Rate from HumanResources.EmployeePayHistory
)
SELECT * FROM CTE
    WHERE [Rank]=3
 
Share this answer
 
Comments
kankeyan 22-Oct-13 4:23am    
This case we need to use dense_rank not row_number. If rate is equal row_number will not work properly dense_rank is right solution.
Madhu Nair 22-Oct-13 4:55am    
You are absolutely right in case if we want to rank someone based on points earned then dense_rank function is the correct function to use. But for the above scenario we only wanted to know the 3rd highest salary. When dense_rank is in this function and two employees are having the same 3rd highest salary figure, then two rows will be returned, while actually the expected result should be one row with 3rd highest salary
kankeyan 22-Oct-13 6:44am    
Use Top 1 or distinct. If sal is 1000,999,999,998. If you use RowNumber third highest salary is 999 is it correct?
Hello ,
this is the query to find third highest salary..

select MIN(salary) from Table_1 where salary in (select distinct Top 3 (salary) from Table_1 order by salary desc)


thanks
 
Share this answer
 
This one is without using any functions,just another way to do it

SQL
select id ,name,salary as topSalary from 
(
SELECT top 3 id ,name,salary FROM table order by salary desc
) AS tbl ORDER BY salasy ASC
 
Share this answer
 
SQL
WITH rk
AS
(
SELECT dense_rank() OVER (ORDER BY RATE DESC)[Rank],Rate from HumanResources.EmployeePayHistory
)
SELECT * FROM rk
    WHERE [Rank]=3
 
Share this answer
 
Comments
[no name] 21-Oct-13 6:57am    
copied solution.
kankeyan 21-Oct-13 8:07am    
Copied solution only for this case we need to use dense_rank not row_number. If rate is equal row_number will not work properly dense_rank is right solution.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900