One of my favorite technical interview question on SQL Server is "What query would you write to find the second highest column value in a table?" The question seems easy, however most developers fail to answer to this. If we had a table named Employee which had a column named Salary and we had to find the second highest Salary in the Employee table, the query for the same would be:

SELECT TOP 1 Salary FROM (SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) AS E ORDER BY Salary ASC

If we ran the above query against the Employee table which had the following 5 rows:

1000
2000
3000
4000
5000

The subquery or the inner query would return the top 2 rows in descending Salary order which would be:

5000
4000

The outer query would then select the top 1 row from the subquery results in ascending Salary order which would be:

4000

Note that if we had to get the fourth highest Salary, we could do so by simply changing the subquery from TOP 2 to TOP 4

Cheers,
Raj

~~~ CODING FOR ETERNITY !!! ~~~