Wednesday, 25 May 2011

SQL SERVER: Finding nth Highest Value

--Select 4th Highest Salary (1-way)

SELECT TOP 1 E_ID FROM (SELECT DISTINCT TOP 4 E_ID FROM EMP ORDER BY E_ID DESC)A ORDER BY E_ID ASC


--Select 4th Highest Salary (2-way)

SELECT MIN(E_ID) FROM (SELECT TOP 4 E_ID FROM EMP ORDER BY E_ID DESC)E




Description:-

This is one of the best query that I posted ,when I got an interesting question regarding this.Use of sub-queries makes complex perforemance.