Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
How to Print Top 5th salaried employee details without using top keyword and without using subqueries?
Posted
Comments
Kornfeld Eliyahu Peter 15-Dec-14 5:05am    
http://mattgemmell.com/what-have-you-tried/
DamithSL 15-Dec-14 5:06am    
what is your database server?
rajugknr 15-Dec-14 5:59am    
sql2008

You can use ranking function

SQL
With empinfo
As
(
select empName,Salary,Row_Number() Over ( order by salary desc ) as RowID
from empinfotable
)

select * from  empinfo where RowID=5


you can try this also

SQL
SELECT empname,salary FROM Employees ORDER BY salary DESC OFFSET 4 ROWS FETCH NEXT 1 ROWS ONLY; 


which works in SQL server 2012 and above

check this link for more detail

OFFSET FETCH Clause
 
Share this answer
 
v5
Comments
rajugknr 15-Dec-14 5:13am    
can we write in single query..
Shweta N Mishra 15-Dec-14 5:26am    
select * from
(
select empName,Salary,Row_Number() Over ( order by salary desc ) as RowID
from empinfotable
) where RowID=5

which sql version you are using ?

you can try this also

SELECT empname,salary FROM Employees ORDER BY salary DESC OFFSET 4 ROWS FETCH NEXT 1 ROWS ONLY;

which works SQL server 2012 & above

check this link for more detail

http://technet.microsoft.com/en-us/library/gg699618%28v=sql.110%29.aspx
/\jmot 15-Dec-14 5:33am    
5+,
and move the code to your answer, so anybody can see.
Shweta N Mishra 15-Dec-14 6:08am    
thank you :)
rajugknr 15-Dec-14 5:50am    
sql 2008 not working second FETCH...
try..

SQL
select * from 
(
    select   ROW_NUMBER() over(order by salary desc) as row1,
             empName,Salary  
    from 
   empinfotable
)[MyCats]
 where row1=5
 
Share this answer
 
v3
Comments
rajugknr 15-Dec-14 5:53am    
not to use sub queries...
/\jmot 15-Dec-14 5:57am    
srry, i forgot. :P
/\jmot 15-Dec-14 5:58am    
ignore my answers.
see @Shweta N Mishra answer,explainede very well.

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