Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Find Min/Max Values in a Set

By Dinesh Asanka,

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.

Total article views: 11400 | Views in the last 30 days: 7
 
Related Articles
FORUM

Selecting distinct records

Selecting distinct records

FORUM

DISTINCT

DISTINCT

FORUM

Speed Up SELECT DISTINCT Queries

How to run a select distinct faster?

FORUM

Select Distinct

Issue with distinct clause.

FORUM

syntax error in select distinct

syntax error in select distinct

Tags
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones