Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Select min and max values along with other SQL Server columns using T-SQL


By:   |   Read Comments (1)   |   Related Tips: 1 | 2 | More > T-SQL

Attend these FREE MSSQLTips webcasts >> click to register


Problem

I have a dataset where I need to find the minimum and maximum values in a SQL Server table. In the case where multiple rows contain the same minimum or maximum value I only want to select the first occurrence. How can this be done with T-SQL code in SQL Server?

Solution

During a recent research project working with climate data, I needed to select the min and max values for barometric pressure for every given year, and then pull the latitude and longitude where those mins and maxes occurred. We will do something similar for this tip.

Create Sample Dataset

For this tip, let's begin by creating a table and inserting some example rows. The CREATE TABLE statement below has a primary key column, integer columns for the YearMonth, Year and Month, the recorded value for which we are finding the min and max, and then a couple of attribute columns whose data values we need to report.

create table dbo.tblMinMaxExample
(
  pk integer not null identity(1,1) primary key,
  dataYearMonth integer,
  dataYear integer,
  dataMonth integer,
  recordedValue float,
  attributeA varchar(1),
  attributeB varchar(1)
)

The INSERT statements below will insert 24 records representing 2 years of monthly data.

insert into dbo.tblMinMaxExample values (201401, 2014, 1,   200.0, 'A', 'a')
insert into dbo.tblMinMaxExample values (201402, 2014, 2,   400.0, 'B', 'b')
insert into dbo.tblMinMaxExample values (201403, 2014, 3,   600.0, 'C', 'c')
insert into dbo.tblMinMaxExample values (201404, 2014, 4,   800.0, 'D', 'd')
insert into dbo.tblMinMaxExample values (201405, 2014, 5,  1000.0, 'E', 'e')
insert into dbo.tblMinMaxExample values (201406, 2014, 6,  1200.0, 'F', 'f')
insert into dbo.tblMinMaxExample values (201407, 2014, 7,  1100.0, 'G', 'g')
insert into dbo.tblMinMaxExample values (201408, 2014, 8,   900.0, 'H', 'h')
insert into dbo.tblMinMaxExample values (201409, 2014, 9,   700.0, 'I', 'i')
insert into dbo.tblMinMaxExample values (201410, 2014, 10,  500.0, 'J', 'j')
insert into dbo.tblMinMaxExample values (201411, 2014, 11,  300.0, 'K', 'k')
insert into dbo.tblMinMaxExample values (201412, 2014, 12,  100.0, 'L', 'l')
go

insert into dbo.tblMinMaxExample values (201501, 2015, 1,   100.0, 'A', 'a')
insert into dbo.tblMinMaxExample values (201502, 2015, 2,   100.0, 'B', 'b')
insert into dbo.tblMinMaxExample values (201503, 2015, 3,   600.0, 'C', 'c')
insert into dbo.tblMinMaxExample values (201504, 2015, 4,   800.0, 'D', 'd')
insert into dbo.tblMinMaxExample values (201505, 2015, 5,  1000.0, 'E', 'e')
insert into dbo.tblMinMaxExample values (201506, 2015, 6,  1200.0, 'F', 'f')
insert into dbo.tblMinMaxExample values (201507, 2015, 7,  1200.0, 'G', 'g')
insert into dbo.tblMinMaxExample values (201508, 2015, 8,   900.0, 'H', 'h')
insert into dbo.tblMinMaxExample values (201509, 2015, 9,   700.0, 'I', 'i')
insert into dbo.tblMinMaxExample values (201510, 2015, 10,  500.0, 'J', 'j')
insert into dbo.tblMinMaxExample values (201511, 2015, 11,  400.0, 'K', 'k')
insert into dbo.tblMinMaxExample values (201512, 2015, 12,  300.0, 'L', 'l')
go

select * from dbo.tblMinMaxExample

After inserting the rows, we will select all columns and rows in our table to verify all is well.

Examining values inserted into the table

The Problem

 Notice in the data above that for 2014 data there is one min value (100) and one max (1200), but for 2015 there are two min values (100) and two max values (1200).  I can do a GROUP BY query like below to find the min and max values, but this won't allow me to get the other column values that I need.

Verifying the min and max values per year.

Solving the Problem

Our code below will handle this situation where we need a tiebreaker, so we can select just one row and we can pull back the other columns.

Let's begin with the minimum value. Notice in the T-SQL code below that we are joining the table to itself using a LEFT OUTER JOIN. The trick is to find the row that causes the t2 attribute values to be NULL.

SELECT t1.recordedValue AS minValue, t1.dataYear, t1.dataMonth,  
t1.attributeA, t1.attributeB
FROM dbo.tblMinMaxExample AS t1
LEFT OUTER JOIN dbo.tblMinMaxExample AS t2
ON t1.dataYear = t2.dataYear
AND 
(
   t1.recordedValue > t2.recordedValue  --min
   OR (
        t1.recordedValue = t2.recordedValue 
  AND t1.dataYearMonth > t2.dataYearMonth
   ) --tiebreaker takes the smaller yearMonth value
) 
WHERE t2.dataYear IS NULL
ORDER BY t1.dataYear ASC

Query 1

In our results below, notice how the minimum for 2014, which is found in the month of December, is returned. Also notice how the minimum for 2015 is calculated to be in the month of January.  This is because our tiebreaker clause in Line 64 selects the earliest month.

Results of the query to find the min.

Query 2

If we change the value of our inequality symbol for our tiebreaker clause in Line 64 from > to < , then the minimum for 2015 is calculated to be in the month of February which is the latest month with that value.

Results of the query to find the min with tiebreaker reversed.

Query 3

Below I have the same code as Query 2, but to find the max value, we reverse the inequality sign in Line 77 from > to < and this will return the max value and the latest month this occurred.

Results of the query to find the max.
Next Steps

There are other ways to break the tie when more than one record contains the min or max. Also, make sure to test your code to ensure accuracy. Finally, please check out these other tips and tutorials on T-SQL on MSSQLTips.com.



Last Update:


signup button

next tip button



About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, August 22, 2016 - 1:08:30 PM - jeff_yao Back To Top

There are better ways (in terms of performance and comprehension) with t-sql window functions, see below

-- find min value with all columns

-- to find max value, just changing ASC to DESC in "partition by dataYear order by recordedValue asc" ) 

; with c as (

select *, rnk = rank() over (partition by datayear order by recordedValue ASC, dataYearMonth desc)

from  dbo.tblMinMaxExample)

select MinValue=recordedValue

, dataYear

, dataMonth

, attributeA

, attributeB

from c

where rnk =1

order by dataYear asc

 


Learn more about SQL Server tools