A friend of mine was asked in an interview to write a query to fetch the second highest marks in a table of students.
Well, I found out 2 ways to do this:
1) Using TOP to get the topmost 2 rows from the table in descending order and then again getting a TOP of the resultset.
SELECT TOP 1 *
FROM(SELECT TOP 2 *
ORDER BY Marks desc)
ORDER BY Marks;
2) Using the MAX function. (I was suprised to see this work)
SELECT Max(Marks) FROM Table1
WHERE Marks Not In (SELECT MAX(Marks) FROM Table1);