Finding the maximum value in multiple columns in a SQL Server table

Find the maximum value for multiple columns in one row in a SQL Server table

Sometimes we need to find the largest value from multiple identical joins (the same data type for these columns) and show

Here is an example

IF(object_id('tempdb: # #TestTable') is  not NULL)    DROP TABLE# #TestTableCREATE TABLE# #TestTable (IDINT IDENTITY(1,1)PRIMARY KEY, NameNVARCHAR( +), Updatebyapp1dateDATETIME, Updatebyapp2dateDATETIME, Updatebyapp3dateDATETIME)INSERT  into# #TestTable (Name, Updatebyapp1date, Updatebyapp2date, updatebyapp3date)VALUES('ABC','2015-08-05','2015-08-04','2015-08-06'),      ('Newcopmany','2014-07-05','2012-12-09','2015-08-14'),      ('MyCompany','2015-03-05','2015-01-14','2015-07-26')      SELECT *  from# #TestTable

The results are as follows

There are three ways to achieve

Method One

SELECT   ID,        Name,        SELECT    MAX(lastupdatedate)           from      VALUES  (updatebyapp1date), (updatebyapp2date),                    as  updatedate ( Lastupdatedate)          as  lastupdatedatefrom    # #TestTable

Method Two

SELECTID,[Name] ,        MAX(updatedate) aslastupdatedate from# #TestTable UNPIVOT (updatedate forDatevalinch(Updatebyapp1date, Updatebyapp2date, updatebyapp3date)) asuGROUP  byID, Name

Method Three

SELECTID, name, (SELECT    MAX(updatedate) aslastupdatedate from(SELECTTt. Updatebyapp1date asupdatedateUNION                      SELECTTT. Updatebyapp2dateUNION                      SELECTTT. Updatebyapp3date) UD) lastupdatedate from# #TestTable TT

The first method uses the VALUES clause to construct each row of data as a table with only one field, and to find the maximum value later, it is very ingenious

The second method uses row-to-column to convert and then display by using the UNPIVOT keyword that is commonly used

The third method is similar to the first method, but uses union to combine three updatebyappdate fields into a result set of only one field and then to find the maximum value

Execution plan for the first method

Execution plan for the second method

Implementation plan for the third method

Overall, the first approach to the execution plan is the best

Note that grouping is not involved here

IF(object_id('tempdb: # #TestTable') is  not NULL)    DROP TABLE# #TestTableCREATE TABLE# #TestTable (IDINT IDENTITY(1,1)             PRIMARY KEY, NameNVARCHAR( +), Updatebyapp1dateDATETIME, Updatebyapp2dateDATETIME, Updatebyapp3dateDATETIME    )INSERT   into# #TestTable (Name, Updatebyapp1date, Updatebyapp2date, updatebyapp3date)VALUES('ABC','2015-08-05','2015-08-04','2015-08-06' ),        ( 'ABC','2015-07-05','2015-06-04','2015-09-06' ),        ( 'Newcopmany','2014-07-05','2012-12-09','2015-08-14' ),        ( 'MyCompany','2015-03-05','2015-01-14','2015-07-26' )      SELECT  * from# #TestTableSELECTID, Name, (SELECT    MAX(lastupdatedate) from(VALUES( updatebyapp1date), (Updatebyapp2date), (updatebyapp3date)) asupdatedate (lastupdatedate)) aslastupdatedate from# #TestTable

If the Name column is the same, it is not possible to derive the maximum value after the name grouping, so be aware

Reprinted from: https://www.mssqltips.com/sqlservertip/4067/find-max-value-from-multiple-columns-in-a-sql-server-table/

Finding the maximum value in multiple columns in a SQL Server table