Friday, December 23, 2005

Select second highest record with a query

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 *
FROM Table1
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);