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
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.