SQL Server 2022 – GREATEST and LEAST

Have you had an opportunity to play around with SQL Server 2022 yet?

A quick Google search will return plenty of great blog posts and articles about new features. One new feature included in the upcoming release is the pair of GREATEST and LEAST functions.

Let’s dig into how GREATEST/LEAST compares with MAX/MIN and look at a short example of them in SQL Server 2022.

GREATEST/LEAST and MAX/MIN Difference

While the words are considered synonyms, these functions serve different purposes. MAX/MIN can be used to get the maximum values and minimum values across rows. On the other hand, GREATEST/LEAST will let you get greatest and least values across columns.

To the Top

We’ll start with a smaller version of the StackOverflow2013 Posts table. If we wanted to know the most recent dates for CreationDate, LastActivityDate, and LastEditDate in the table, we could use MAX and run:

SELECT MAX(CreationDate) AS 'Max_CreationDate'
	,MAX(LastActivityDate) AS 'Max_LastActivityDate'
	,MAX(LastEditDate) AS 'Max_LastEditDate'
FROM Posts;
GO

Let’s change it up and focus on just the first five rows:

Perhaps we want to know if a post CreationDate, LastActivityDate, or LastEditDate value was the most recent for each Post record. This is where we would use GREATEST. We could run the following:

SELECT Id
	,GREATEST(CreationDate, LastActivitydate, LastEditDate) AS 'Greatest_Date'
FROM Posts;
GO

Finding the opposite isn’t too difficult to determine. We’ll substitute GREATEST with LEAST to find the oldest dates for each post:

SELECT Id
	,LEAST(CreationDate, LastActivitydate, LastEditDate) AS 'Least_Date'
FROM Posts;
GO

We can put them together and make our ranges more clear:

SELECT Id
	,GREATEST(CreationDate, LastActivitydate, LastEditDate) AS 'Greatest_Date'
	,LEAST(CreationDate, LastActivitydate, LastEditDate) AS 'Least_Date'
FROM Posts;
GO

Great to Have

We’ve now seen the difference between the common MAX/MIN and the new GREATEST/LEAST functions in SQL Server 2022. These aren’t not necessarily brand new as they are currently available in Azure. Either way, the next time you need to find the GREATEST or LEAST value, the GREATEST or LEAST functions can make your life easier.

Thanks for reading!