In Oracle Magazine there was a discussion about finding the Nth Max or Min

value from a value set. After three issues of magazine it came across with the

following query as the solution to the problem.

Select Min(Col1) From 
(Select Col1 From (Select Distinct Col1 From Tab1 Order By Col1 Desc)
Where RowNum <=&N

I was trying to do the same with SQL Server. But I found that there is no

field name called ROWNUM in SQLServer. Then I posted it into the Discussion

board of SQLServerCentral.Com. You can see that link named RowNum Function in

SQLServer. After studying this discussion I felt that there is no direct method

of doing it like in Oracle. This might be included in the next version of the

SQL Server!

I commenced the work with a Simple table called NMaxMin.

CREATE TABLE [NMaxMin] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Number] [int] NULL 
) ON [PRIMARY]
GO

I filled some arbitrary data in to the NMaxMin table. Figure 1 shows the set

of vales used for this discussion.

I wrote the below query to get the result set

starting from minimum to maximum along with sequence number.

select rank=count(*), s1.number from (Select distinct(number) from NMaxMin) 
s1,
(Select distinct(number) from NMaxMin) s2 where s1.number >= s2.number  
group by s1.number  order by 1

After running the query the output will be like in Figure 2.

Now you can see there are only 11 records (Previously there were 14 records.

This has happened because there are 2 records of 1’s and 3 records of 45’s. From

the above table now it will be easy to find out the Nth maximum. If you want the

5th maximum value, query will be:

Select number From (select count(*) as rank , s1.number
from (Select distinct(number) from NMaxMin) s1,
(Select distinct(number) from NMaxMin) s2
where s1.number <= s2.number
group by s1.number ) s3 where s3.rank = 5

Answer will be 567 which is the 5th maximum number in the table. For the minimum

you just have to do a small change to the query.

Select rank, number From (select count(*) as rank , s1.number
from (Select distinct(number) from NMaxMin) s1,
(Select distinct(number) from NMaxMin) s2
where s1.number >= s2.number
group by s1.number ) s3 where rank = 5

Answer will be 78 which is the 5th minimum number in the table. Maximum and

Minimum numbers are useful when you are doing statistical calculations.