For our example first generate a table with random number values using the Rand() function

Set noCount on

–Declare a Table

Declare @Table1 Table(id int,Rndvalue int)

Declare @index int

select @index=1

–insert 10 Random values into the Table

while @index<11

Begin

–Generate Random Values

Insert into @Table1 values(@index,ceiling(1000*Rand()))

–Increment the id value

Select @index=@index+1

end

–Select the values

Select id,RndValue from @Table1

Random Values insertion

–First Maximum

Select 1 as [Max],Max(RndValue)as [1st Max] From @Table1

First Max

–Second Maximum

Select 2 as [Max],* From @Table1 where RndValue =(Select max(RndValue) From @Table1 where RndValue <(Select max(RndValue) From @Table1))

Second Max using Subquery

–Third Maximum

Select 3 as [Max],* From @Table1 where RndValue =(Select max(RndValue) From @Table1 where RndValue <(Select max(RndValue) From @Table1 WHERE RndValue <(Select max(RndValue) From @Table1)))

Third max using Subquery

–Using Correlated SubQuery

Select 3 AS [Max],* From @Table1 A Where (3-1)=(Select Count(Distinct(B.RndValue)) FROM @Table1 B WHERE B.RndValue >A.RndValue)

3 rd Max using Correlated Subquery

Syntax

Select * FROM Table1 A Where (N-1)=(SELECT Count(Distinct(B.ColName)) FROM Table1 B WHERE B.ColName > A.ColName

–Using Top Keyword

Select Top 1 RndValue from(Select Top 3 RndValue From @Table1 Order By RndValue Desc) a Order By RndValue Asc

using TOP operator

–Using aggregate function

Select min(RndValue) From (Select Top 2 RndValue From @Table1 Order By RndValue Desc) T

using Aggregate function

–Using RowNumber() in SQL SERVER 2005

SELECT T.* FROM (SELECT ROW_NUMBER()over(Order By RndValue DESC) AS RowValue,id,RndValue FROM @Table1)T Where T.RowValue=3

Using SQL SERVER 2005 ROW_NUMBER()