Posted by: R Manimaran | July 4, 2008

Finding Nth Maximum values From a SQL Server Table

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

Random Values insertion

–First Maximum

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

First Max

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

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

Third max using Subquery

–Using Correlated SubQuery

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

3 rd Max using Correlated Subquery

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 TOP operator

Using aggregate function

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

using Aggreate function

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()

Using SQL SERVER 2005 ROW_NUMBER()

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: