Finding nth maximum number in SQL Server 2005

This is the frequent requirement for the developer to find the nth max number from the table. It will easy to get it if you are using SQL Server 2005, as its allows us to make the top query variable.

Please read the Flexibility using TOP clause in SQL Server 2005 for more details.

Lets say, we are having student and their marks, and we want the nth max mark. First will see the table structure and will add few data into it.

SET NOCOUNT ON
DECLARE @Marks TABLE
(
    StudName VARCHAR(100),
    Mark INT
)

INSERT INTO @Marks VALUES('AAAAA', 55)
INSERT INTO @Marks VALUES('BBBBB', 65)
INSERT INTO @Marks VALUES('CCCCC', 59)
INSERT INTO @Marks VALUES('DDDDD', 52)
INSERT INTO @Marks VALUES('FFFFF', 65)
INSERT INTO @Marks VALUES('EEEEE', 95)

SELECT Mark FROM @Marks ORDER BY Mark DESC

Mark
-----------
95
65
65
59
55
52

Now we write the query which allow us to find nth max mark form this list.

DECLARE @Top INT
SET @Top = 2

SELECT MIN(Mark) AS 'Top' FROM(
SELECT DISTINCT TOP (@Top) Mark FROM @Marks ORDER BY Mark DESC) A

Top
-----------
65

@Top is variable; which gives you the ability to fetch Nth max.

Flexibility using TOP clause in SQL Server 2005

Hello all,

Upto now we know that how to use TOP clause in Sql Server 2000 . SQL Server 2005 come up with more flexible way to use TOP clause.

Here is the simple way to get TOP 10 [or say 'n' a dynamic number] from a table.

DECLARE @Rows INT
SET @Rows = 10

SELECT TOP ( @Rows ) *
FROM TempMaster

This will return the top 10 rows from TempMaster. You can also replace @Rows with anything that evaluates to a number.

Now look at the following query; its odd but runs just fine:

SELECT TOP ( SELECT COUNT(*) FROM TempMaster ) *
FROM TempDetails

You can also use the TOP clause for INSERT, UPDATE and DELETE statements. If you wanted to DELETE in batches of 500 you can now do that using the TOP clause.