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

By: Dallas Snider | Comments (1) | Related: 1 | 2 | > Functions System

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.

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.

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.

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips