{"id":97983,"date":"2023-09-18T20:53:28","date_gmt":"2023-09-18T20:53:28","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=97983"},"modified":"2023-10-09T13:28:19","modified_gmt":"2023-10-09T13:28:19","slug":"alternatives-to-sql-2022-built-in-function-greatest","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/alternatives-to-sql-2022-built-in-function-greatest\/","title":{"rendered":"Alternatives To SQL Server 2022 Built-in function GREATEST"},"content":{"rendered":"

If you haven’t already heard, SQL Server 2022\u00a0 introduced a new built-in system function called GREATEST<\/a>. Simply put, it is to a set of columns, variables, expressions etc. what the MAX<\/code> function is to a set of values (i.e., rows) of a single column or expression. The opposite of GREATEST<\/code> function is LEAST<\/code> function which returns the smallest value from the supplied list of columns, variables, constants etc. GREATEST<\/code> & LEAST<\/code> can be considered a pair, just as MIN<\/code>\/MAX<\/code> functions are.<\/p>\n

So, on a SQL Server 2022 instance, you can simply call GREATEST<\/code> function with list of columns, variables, or constant values to get the highest value out of all of them. Here is an example with list of constants:<\/p>\n

SELECT GREATEST(500, 10, 3, 50, 200, 8) [Greatest],\r\n       GREATEST(10, 500, 3, 50, 200, 8) [Greatest2],\r\n       GREATEST(200, 10, 3, 50, 500, 8) [Greatest3]<\/pre>\n
This will return:<\/p>\n
\"A<\/p>\n
In the same manner,<\/p>\n
I can also pass a list of columns to it:<\/p>\n
with cte as\r\n(\r\nselect \r\n      DB_NAME(database_id) db_name,\r\n      max(last_user_seek) last_user_seek,\r\n      max(last_user_scan) last_user_scan,\r\n      max(last_user_lookup) last_user_lookup,\r\n      max(last_user_update) last_user_update\r\nfrom sys.dm_db_index_usage_stats\r\ngroup by DB_NAME(database_id)\r\n)\r\nselect \r\n      *,\r\n      GREATEST(last_user_seek, last_user_scan, \r\n      last_user_lookup,last_user_update) last_access_time_cte\r\nfrom cte;<\/pre>\n
This returns the following:<\/p>\n
<\/p>\n
And while it is not as easy to read, note that you can pass in expressions as well, as you can get the same result by pushing the GREATEST<\/code> function up into the primary query too:<\/p>\n
select DB_NAME(database_id) db_name, \r\n       max(last_user_seek) last_user_seek, \r\n       max(last_user_scan) last_user_scan, \r\n       max(last_user_lookup) last_user_lookup, \r\n       max(last_user_update) last_user_update, \r\n       GREATEST(max(last_user_seek), \r\n                max(last_user_scan), \r\n                max(last_user_lookup), \r\n                max(last_user_update)) AS last_access_time_cte \r\nfrom sys.dm_db_index_usage_stats \r\ngroup by DB_NAME(database_id);<\/pre>\n
Isn’t that a nifty feature?<\/p>\n
This rest of this article, focuses on how to achieve the same functionality in pre-SQL Server 2022 versions of SQL Server. Same approaches can be used to mimic the functionality of LEAST<\/code> function as well.<\/p>\n
I recently had to remind a client of mine that we are past the halfway mark of year 2023 that they still didn’t have a single SQL Server 2022 server, even though their SQL Server licenses covered it. I would not be surprised if there are many organizations that may still have a sizable number of SQL Servers pre-SQL 2022.<\/p>\n
I personally though, am looking forward to when the next release of SQL Server will come out, whether it would be in 2024, 2025, or 2026? Hopefully Microsoft doesn’t make us wait beyond the year 2026.<\/p>\n
In this article I am going to demonstrate 2 viable alternative solutions to find the largest value of a group of values in your queries (not including writing a very complicated CASE<\/code> expression!)<\/p>\n