<p>How do you return 1 value per row of the max of several columns:</p>

<p><strong>TableName</strong></p>

<pre><code>[Number, Date1, Date2, Date3, Cost]
</code></pre>

<p>I need to return something like this:</p>

<pre><code>[Number, Most_Recent_Date, Cost]
</code></pre>

<p>Query?</p>

[{'answer_id': 71045, 'author': 'Lasse V. Karlsen', 'author_id': 267, 'author_profile': 'https://Stackoverflow.com/users/267', 'pm_score': 9, 'selected': True, 'text': '<p>This is an old answer and broken in many way.</p>\n<p>See <a href="https://stackoverflow.com/a/6871572/194653">https://stackoverflow.com/a/6871572/194653</a> which has way more upvotes and works with sql server 2008+ and handles nulls, etc.</p>\n<p><strong>Original but problematic answer</strong>:</p>\n<p>Well, you can use the CASE statement:</p>\n<pre><code>SELECT\n    CASE\n        WHEN Date1 &gt;= Date2 AND Date1 &gt;= Date3 THEN Date1\n        WHEN Date2 &gt;= Date1 AND Date2 &gt;= Date3 THEN Date2\n        WHEN Date3 &gt;= Date1 AND Date3 &gt;= Date2 THEN Date3\n        ELSE                                        Date1\n    END AS MostRecentDate\n</code></pre>\n'}, {'answer_id': 71147, 'author': 'databyss', 'author_id': 9094, 'author_profile': 'https://Stackoverflow.com/users/9094', 'pm_score': 4, 'selected': False, 'text': '<p>Either of the two samples below will work:</p>\n\n<pre><code>SELECT  MAX(date_columns) AS max_date\nFROM    ( (SELECT   date1 AS date_columns\n           FROM     data_table         )\n          UNION\n          ( SELECT  date2 AS date_columns\n            FROM    data_table\n          )\n          UNION\n          ( SELECT  date3 AS date_columns\n            FROM    data_table\n          )\n        ) AS date_query\n</code></pre>\n\n<p>The second is an add-on to <a href="https://stackoverflow.com/users/267/lassevk">lassevk\'s</a> answer.</p>\n\n<pre><code>SELECT  MAX(MostRecentDate)\nFROM    ( SELECT    CASE WHEN date1 &gt;= date2\n                              AND date1 &gt;= date3 THEN date1\n                         WHEN date2 &gt;= date1\n                              AND date2 &gt;= date3 THEN date2\n                         WHEN date3 &gt;= date1\n                              AND date3 &gt;= date2 THEN date3\n                         ELSE date1\n                    END AS MostRecentDate\n          FROM      data_table\n        ) AS date_query \n</code></pre>\n'}, {'answer_id': 331873, 'author': 'bajafresh4life', 'author_id': 21339, 'author_profile': 'https://Stackoverflow.com/users/21339', 'pm_score': 8, 'selected': False, 'text': "<p>If you're using MySQL or PostgreSQL or Oracle or BigQuery, you can use</p>\n<pre><code>SELECT GREATEST(col1, col2 ...) FROM table\n</code></pre>\n"}, {'answer_id': 331933, 'author': 'Lance Fisher', 'author_id': 571, 'author_profile': 'https://Stackoverflow.com/users/571', 'pm_score': 2, 'selected': False, 'text': "<p>If you are using SQL Server 2005, you can use the UNPIVOT feature.  Here is a complete example:</p>\n\n<pre><code>create table dates \n(\n  number int,\n  date1 datetime,\n  date2 datetime,\n  date3 datetime \n)\n\ninsert into dates values (1, '1/1/2008', '2/4/2008', '3/1/2008')\ninsert into dates values (1, '1/2/2008', '2/3/2008', '3/3/2008')\ninsert into dates values (1, '1/3/2008', '2/2/2008', '3/2/2008')\ninsert into dates values (1, '1/4/2008', '2/1/2008', '3/4/2008')\n\nselect max(dateMaxes)\nfrom (\n  select \n    (select max(date1) from dates) date1max, \n    (select max(date2) from dates) date2max,\n    (select max(date3) from dates) date3max\n) myTable\nunpivot (dateMaxes For fieldName In (date1max, date2max, date3max)) as tblPivot\n\ndrop table dates\n</code></pre>\n"}, {'answer_id': 1398019, 'author': 'Niikola', 'author_id': 130904, 'author_profile': 'https://Stackoverflow.com/users/130904', 'pm_score': 6, 'selected': False, 'text': "<p>There are 3 more methods where <code>UNPIVOT</code> (1) is the fastest by far, followed by Simulated Unpivot (3) which is much slower than (1) but still faster than (2)</p>\n\n<pre><code>CREATE TABLE dates\n    (\n      number INT PRIMARY KEY ,\n      date1 DATETIME ,\n      date2 DATETIME ,\n      date3 DATETIME ,\n      cost INT\n    )\n\nINSERT  INTO dates\nVALUES  ( 1, '1/1/2008', '2/4/2008', '3/1/2008', 10 )\nINSERT  INTO dates\nVALUES  ( 2, '1/2/2008', '2/3/2008', '3/3/2008', 20 )\nINSERT  INTO dates\nVALUES  ( 3, '1/3/2008', '2/2/2008', '3/2/2008', 30 )\nINSERT  INTO dates\nVALUES  ( 4, '1/4/2008', '2/1/2008', '3/4/2008', 40 )\nGO\n</code></pre>\n\n<h2>Solution 1 (<code>UNPIVOT</code>)</h2>\n\n<pre><code>SELECT  number ,\n        MAX(dDate) maxDate ,\n        cost\nFROM    dates UNPIVOT ( dDate FOR nDate IN ( Date1, Date2,\n                                            Date3 ) ) as u\nGROUP BY number ,\n        cost \nGO\n</code></pre>\n\n<h2>Solution 2 (Sub query per row)</h2>\n\n<pre><code>SELECT  number ,\n        ( SELECT    MAX(dDate) maxDate\n          FROM      ( SELECT    d.date1 AS dDate\n                      UNION\n                      SELECT    d.date2\n                      UNION\n                      SELECT    d.date3\n                    ) a\n        ) MaxDate ,\n        Cost\nFROM    dates d\nGO\n</code></pre>\n\n<h2>Solution 3 (Simulated <code>UNPIVOT</code>)</h2>\n\n<pre><code>;WITH    maxD\n          AS ( SELECT   number ,\n                        MAX(CASE rn\n                              WHEN 1 THEN Date1\n                              WHEN 2 THEN date2\n                              ELSE date3\n                            END) AS maxDate\n               FROM     dates a\n                        CROSS JOIN ( SELECT 1 AS rn\n                                     UNION\n                                     SELECT 2\n                                     UNION\n                                     SELECT 3\n                                   ) b\n               GROUP BY Number\n             )\n    SELECT  dates.number ,\n            maxD.maxDate ,\n            dates.cost\n    FROM    dates\n            INNER JOIN MaxD ON dates.number = maxD.number\nGO\n\nDROP TABLE dates\nGO\n</code></pre>\n"}, {'answer_id': 4308539, 'author': 'Nat', 'author_id': 13813, 'author_profile': 'https://Stackoverflow.com/users/13813', 'pm_score': 3, 'selected': False, 'text': '<pre><code>SELECT \n    CASE \n        WHEN Date1 &gt;= Date2 AND Date1 &gt;= Date3 THEN Date1 \n        WHEN Date2 &gt;= Date3 THEN Date2 \n        ELSE Date3\n    END AS MostRecentDate \n</code></pre>\n\n<p>This is slightly easier to write out and skips evaluation steps as the case statement is evaluated in order.</p>\n'}, {'answer_id': 4308905, 'author': 'Martin Smith', 'author_id': 73226, 'author_profile': 'https://Stackoverflow.com/users/73226', 'pm_score': 3, 'selected': False, 'text': "<pre><code>DECLARE @TableName TABLE (Number INT, Date1 DATETIME, Date2 DATETIME, Date3 DATETIME, Cost MONEY)\n\nINSERT INTO @TableName \nSELECT 1, '20000101', '20010101','20020101',100 UNION ALL\nSELECT 2, '20000101', '19900101','19980101',99 \n\nSELECT Number,\n       Cost  ,\n       (SELECT MAX([Date])\n       FROM    (SELECT Date1 AS [Date]\n               UNION ALL\n               SELECT Date2\n               UNION ALL\n               SELECT Date3\n               )\n               D\n       )\n       [Most Recent Date]\nFROM   @TableName\n</code></pre>\n"}, {'answer_id': 4695172, 'author': 'DrYodo', 'author_id': 576121, 'author_profile': 'https://Stackoverflow.com/users/576121', 'pm_score': 0, 'selected': False, 'text': "<p>You could create a function where you pass the dates and then add the function to the select statement like below.\nselect Number, dbo.fxMost_Recent_Date(Date1,Date2,Date3), Cost</p>\n\n<pre><code>create FUNCTION  fxMost_Recent_Date \n</code></pre>\n\n<p>(\n    @Date1 smalldatetime, \n    @Date2 smalldatetime,\n    @Date3 smalldatetime\n)\nRETURNS smalldatetime\nAS\nBEGIN\n    DECLARE @Result smalldatetime</p>\n\n<pre><code>declare @MostRecent smalldatetime\n\nset @MostRecent='1/1/1900'\n\nif @Date1&gt;@MostRecent begin set @MostRecent=@Date1 end\nif @Date2&gt;@MostRecent begin set @MostRecent=@Date2 end\nif @Date3&gt;@MostRecent begin set @MostRecent=@Date3 end\nRETURN @MostRecent\n</code></pre>\n\n<p>END</p>\n"}, {'answer_id': 4922103, 'author': 'MartinC', 'author_id': 606510, 'author_profile': 'https://Stackoverflow.com/users/606510', 'pm_score': 4, 'selected': False, 'text': '<p>Scalar Function cause all sorts of performance issues, so its better to wrap the logic into an Inline Table Valued Function if possible.  This is the function I used to replace some User Defined Functions which selected the Min/Max dates from a list of upto ten dates.  When tested on my dataset of 1 Million rows the Scalar Function took over 15 minutes before I killed the query the Inline TVF took 1 minute which is the same amount of time as selecting the resultset into a temporary table.  To use this call the function from either a subquery in the the SELECT or a CROSS APPLY.</p>\n\n<pre><code>CREATE FUNCTION dbo.Get_Min_Max_Date\n(\n    @Date1  datetime,\n    @Date2  datetime,\n    @Date3  datetime,\n    @Date4  datetime,\n    @Date5  datetime,\n    @Date6  datetime,\n    @Date7  datetime,\n    @Date8  datetime,\n    @Date9  datetime,\n    @Date10 datetime\n)\nRETURNS TABLE\nAS\nRETURN\n(\n    SELECT      Max(DateValue)  Max_Date,\n                Min(DateValue)  Min_Date\n    FROM        (\n                    VALUES  (@Date1),\n                            (@Date2),\n                            (@Date3),\n                            (@Date4),\n                            (@Date5),\n                            (@Date6),\n                            (@Date7),\n                            (@Date8),\n                            (@Date9),\n                            (@Date10)\n                )   AS Dates(DateValue)\n)\n</code></pre>\n'}, {'answer_id': 6650238, 'author': 'Michael Freidgeim', 'author_id': 52277, 'author_profile': 'https://Stackoverflow.com/users/52277', 'pm_score': 1, 'selected': False, 'text': '<p>Based on the <a href="http://www.experts-exchange.com/M_664986.html" rel="nofollow noreferrer">ScottPletcher</a>\'s  solution from <a href="http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24204894.html" rel="nofollow noreferrer">http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24204894.html</a>\nI’ve created a set of functions (e.g. GetMaxOfDates3 , GetMaxOfDates13 )to find max of up to 13 Date values using UNION ALL.\nSee <a href="https://mfreidge.wordpress.com/2011/07/11/t-sql-function-to-get-maximum-of-values-from-the-same-row/" rel="nofollow noreferrer">T-SQL function to Get Maximum of values from the same row</a>\nHowever I haven\'t considered UNPIVOT solution at the time of writing these functions</p>\n<pre><code>CREATE FUNCTION GetMaxOfDates13 (\n@value01 DateTime = NULL,  \n@value02 DateTime = NULL,\n@value03 DateTime = NULL,\n@value04 DateTime = NULL,\n@value05 DateTime = NULL,\n@value06 DateTime = NULL,\n@value07 DateTime = NULL,\n@value08 DateTime = NULL,\n@value09 DateTime = NULL,\n@value10 DateTime = NULL,\n@value11 DateTime = NULL,\n@value12 DateTime = NULL,\n@value13 DateTime = NULL\n)\nRETURNS DateTime\nAS\nBEGIN\nRETURN (\nSELECT TOP 1 value\nFROM (\nSELECT @value01 AS value UNION ALL\nSELECT @value02 UNION ALL\nSELECT @value03 UNION ALL\nSELECT @value04 UNION ALL\nSELECT @value05 UNION ALL\nSELECT @value06 UNION ALL\nSELECT @value07 UNION ALL\nSELECT @value08 UNION ALL\nSELECT @value09 UNION ALL\nSELECT @value10 UNION ALL\nSELECT @value11 UNION ALL\nSELECT @value12 UNION ALL\nSELECT @value13\n) AS [values]\nORDER BY value DESC    \n)\nEND –FUNCTION\nGO\nCREATE FUNCTION GetMaxOfDates3 (\n@value01 DateTime = NULL,  \n@value02 DateTime = NULL,\n@value03 DateTime = NULL\n)\nRETURNS DateTime\nAS\nBEGIN\nRETURN dbo.GetMaxOfDates13(@value01,@value02,@value03,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)\nEND –FUNCTION\n</code></pre>\n'}, {'answer_id': 6871572, 'author': 'Sven', 'author_id': 442204, 'author_profile': 'https://Stackoverflow.com/users/442204', 'pm_score': 10, 'selected': False, 'text': '<p>Here is another nice solution for the <code>Max</code> functionality using T-SQL and SQL Server</p>\n<pre><code>SELECT [Other Fields],\n  (SELECT Max(v) \n   FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate]\nFROM [YourTableName]\n</code></pre>\n<p>Values is the <a href="https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver15" rel="noreferrer">Table Value Constructor</a>.</p>\n<p>&quot;Specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement. The table value constructor can be specified either as the VALUES clause of an INSERT ... VALUES statement, or as a derived table in either the USING clause of the MERGE statement or the FROM clause.&quot;</p>\n'}, {'answer_id': 8971789, 'author': 'Disillusioned', 'author_id': 224704, 'author_profile': 'https://Stackoverflow.com/users/224704', 'pm_score': 3, 'selected': False, 'text': '<p>Unfortunately <a href="https://stackoverflow.com/a/71045/224704">Lasse\'s answer</a>, though seemingly obvious, has a crucial flaw. It cannot handle NULL values. Any single NULL value results in Date1 being returned. Unfortunately any attempt to fix that problem tends to get extremely messy and doesn\'t scale to 4 or more values very nicely.</p>\n\n<p><a href="https://stackoverflow.com/a/71147/224704">databyss\'s first answer</a> looked (and is) good. However, it wasn\'t clear whether the answer would easily extrapolate to 3 values from a multi-table join instead of the simpler 3 values from a single table. I wanted to avoid turning such a query into a sub-query just to get the max of 3 columns, also I was pretty sure databyss\'s excellent idea could be cleaned up a bit.</p>\n\n<p>So without further ado, here\'s my solution (derived from databyss\'s idea).<br>\nIt uses cross-joins selecting constants to simulate the effect of a multi-table join. The important thing to note is that all the necessary aliases carry through correctly (which is not always the case) and this keeps the pattern quite simple and fairly scalable through additional columns.</p>\n\n<pre><code>DECLARE @v1 INT ,\n        @v2 INT ,\n        @v3 INT\n--SET @v1 = 1 --Comment out SET statements to experiment with \n              --various combinations of NULL values\nSET @v2 = 2\nSET @v3 = 3\n\nSELECT  ( SELECT    MAX(Vals)\n          FROM      ( SELECT    v1 AS Vals\n                      UNION\n                      SELECT    v2\n                      UNION\n                      SELECT    v3\n                    ) tmp\n          WHERE     Vals IS NOT NULL -- This eliminates NULL warning\n\n        ) AS MaxVal\nFROM    ( SELECT    @v1 AS v1\n        ) t1\n        CROSS JOIN ( SELECT @v2 AS v2\n                   ) t2\n        CROSS JOIN ( SELECT @v3 AS v3\n                   ) t3\n</code></pre>\n'}, {'answer_id': 10831815, 'author': 'Luis Miguel Rosa', 'author_id': 1428154, 'author_profile': 'https://Stackoverflow.com/users/1428154', 'pm_score': 2, 'selected': False, 'text': '<p>Problem: choose the minimum rate value given to an entity\nRequirements: Agency rates can be null</p>\n\n<pre><code>[MinRateValue] = \nCASE \n   WHEN ISNULL(FitchRating.RatingValue, 100) &lt; = ISNULL(MoodyRating.RatingValue, 99) \n   AND  ISNULL(FitchRating.RatingValue, 100) &lt; = ISNULL(StandardPoorsRating.RatingValue, 99) \n   THEN FitchgAgency.RatingAgencyName\n\n   WHEN ISNULL(MoodyRating.RatingValue, 100) &lt; = ISNULL(StandardPoorsRating.RatingValue , 99)\n   THEN MoodyAgency.RatingAgencyName\n\n   ELSE ISNULL(StandardPoorsRating.RatingValue, \'N/A\') \nEND \n</code></pre>\n\n<p>Inspired by <a href="https://stackoverflow.com/a/4308539">this answer</a> from <a href="https://stackoverflow.com/users/13813/nat">Nat</a></p>\n'}, {'answer_id': 23864329, 'author': 'TechDo', 'author_id': 1367256, 'author_profile': 'https://Stackoverflow.com/users/1367256', 'pm_score': 1, 'selected': False, 'text': '<p>Please try using <code>UNPIVOT</code>:</p>\n\n<pre><code>SELECT MAX(MaxDt) MaxDt\n   FROM tbl \nUNPIVOT\n   (MaxDt FOR E IN \n      (Date1, Date2, Date3)\n)AS unpvt;\n</code></pre>\n'}, {'answer_id': 23888942, 'author': 'EarlOfEnnui', 'author_id': 3442468, 'author_profile': 'https://Stackoverflow.com/users/3442468', 'pm_score': 2, 'selected': False, 'text': '<p>Using CROSS APPLY (for 2005+) ....</p>\n\n<pre><code>SELECT MostRecentDate \nFROM SourceTable\n    CROSS APPLY (SELECT MAX(d) MostRecentDate FROM (VALUES (Date1), (Date2), (Date3)) AS a(d)) md\n</code></pre>\n'}, {'answer_id': 24527258, 'author': 'abdulbasit', 'author_id': 3678700, 'author_profile': 'https://Stackoverflow.com/users/3678700', 'pm_score': 2, 'selected': False, 'text': '<p>From SQL Server 2012 we can use <a href="http://msdn.microsoft.com/en-us/library/hh213574%28v=sql.110%29.aspx" rel="nofollow">IIF</a>.</p>\n\n<pre><code> DECLARE @Date1 DATE=\'2014-07-03\';\n DECLARE @Date2 DATE=\'2014-07-04\';\n DECLARE @Date3 DATE=\'2014-07-05\';\n\n SELECT IIF(@Date1&gt;@Date2,\n        IIF(@Date1&gt;@Date3,@Date1,@Date3),\n        IIF(@Date2&gt;@Date3,@Date2,@Date3)) AS MostRecentDate\n</code></pre>\n'}, {'answer_id': 29385881, 'author': 'jjaskulowski', 'author_id': 2053494, 'author_profile': 'https://Stackoverflow.com/users/2053494', 'pm_score': 4, 'selected': False, 'text': '<p>For T-SQL (MSSQL 2008+)</p>\n\n<pre><code>SELECT\n  (SELECT\n     MAX(MyMaxName) \n   FROM ( VALUES \n            (MAX(Field1)), \n            (MAX(Field2)) \n        ) MyAlias(MyMaxName)\n  ) \nFROM MyTable1\n</code></pre>\n'}, {'answer_id': 31914496, 'author': 'danvasiloiu', 'author_id': 4424087, 'author_profile': 'https://Stackoverflow.com/users/4424087', 'pm_score': -1, 'selected': False, 'text': '<p>here is a good solution:</p>\n\n<pre><code>CREATE function [dbo].[inLineMax] (@v1 float,@v2 float,@v3 float,@v4 float)\nreturns float\nas\nbegin\ndeclare @val float\nset @val = 0 \ndeclare @TableVal table\n(value float )\ninsert into @TableVal select @v1\ninsert into @TableVal select @v2\ninsert into @TableVal select @v3\ninsert into @TableVal select @v4\n\nselect @val= max(value) from @TableVal\n\nreturn @val\nend \n</code></pre>\n'}, {'answer_id': 37784473, 'author': 'claudio', 'author_id': 6458642, 'author_profile': 'https://Stackoverflow.com/users/6458642', 'pm_score': -1, 'selected': False, 'text': '<p>I do not know if it is on SQL, etc... on M$ACCESS help there is a function called <code>MAXA(Value1;Value2;...)</code> that is supposed to do such.</p>\n\n<p>Hope can help someone.</p>\n\n<p>P.D.: Values can be columns or calculated ones, etc.</p>\n'}, {'answer_id': 49515362, 'author': 'M.A.Bell', 'author_id': 8889436, 'author_profile': 'https://Stackoverflow.com/users/8889436', 'pm_score': 0, 'selected': False, 'text': '<p>Another way to use <strong>CASE WHEN</strong> </p>\n\n<pre><code>SELECT CASE true \n       WHEN max(row1) &gt;= max(row2) THEN CASE true WHEN max(row1) &gt;= max(row3) THEN max(row1) ELSE max(row3) end ELSE\n       CASE true WHEN max(row2) &gt;= max(row3) THEN max(row2) ELSE max(row3) END END\nFROM yourTable\n</code></pre>\n'}, {'answer_id': 54612018, 'author': 'Robert Lujo', 'author_id': 565525, 'author_profile': 'https://Stackoverflow.com/users/565525', 'pm_score': 1, 'selected': False, 'text': "<p>I prefer solutions based on case-when, my assumption is that it should have the least impact on possible performance drop compared to other possible solutions like those with cross-apply, values(), custom functions etc.</p>\n\n<p>Here is the case-when version that handles null values with most of possible test cases:</p>\n\n<pre><code>SELECT\n    CASE \n        WHEN Date1 &gt; coalesce(Date2,'0001-01-01') AND Date1 &gt; coalesce(Date3,'0001-01-01') THEN Date1 \n        WHEN Date2 &gt; coalesce(Date3,'0001-01-01') THEN Date2 \n        ELSE Date3\n    END AS MostRecentDate\n    , *\nfrom \n(values\n     (  1, cast('2001-01-01' as Date), cast('2002-01-01' as Date), cast('2003-01-01' as Date))\n    ,(  2, cast('2001-01-01' as Date), cast('2003-01-01' as Date), cast('2002-01-01' as Date))\n    ,(  3, cast('2002-01-01' as Date), cast('2001-01-01' as Date), cast('2003-01-01' as Date))\n    ,(  4, cast('2002-01-01' as Date), cast('2003-01-01' as Date), cast('2001-01-01' as Date))\n    ,(  5, cast('2003-01-01' as Date), cast('2001-01-01' as Date), cast('2002-01-01' as Date))\n    ,(  6, cast('2003-01-01' as Date), cast('2002-01-01' as Date), cast('2001-01-01' as Date))\n    ,( 11, cast(NULL         as Date), cast('2002-01-01' as Date), cast('2003-01-01' as Date))\n    ,( 12, cast(NULL         as Date), cast('2003-01-01' as Date), cast('2002-01-01' as Date))\n    ,( 13, cast('2003-01-01' as Date), cast(NULL         as Date), cast('2002-01-01' as Date))\n    ,( 14, cast('2002-01-01' as Date), cast(NULL         as Date), cast('2003-01-01' as Date))\n    ,( 15, cast('2003-01-01' as Date), cast('2002-01-01' as Date), cast(NULL         as Date))\n    ,( 16, cast('2002-01-01' as Date), cast('2003-01-01' as Date), cast(NULL         as Date))\n    ,( 21, cast('2003-01-01' as Date), cast(NULL         as Date), cast(NULL         as Date))\n    ,( 22, cast(NULL         as Date), cast('2003-01-01' as Date), cast(NULL         as Date))\n    ,( 23, cast(NULL         as Date), cast(NULL         as Date), cast('2003-01-01' as Date))\n    ,( 31, cast(NULL         as Date), cast(NULL         as Date), cast(NULL         as Date))\n\n) as demoValues(id, Date1,Date2,Date3)\norder by id\n;\n</code></pre>\n\n<p>and the result is:</p>\n\n<pre><code>MostRecent    id   Date1      Date2      Date3\n2003-01-01    1    2001-01-01 2002-01-01 2003-01-01\n2003-01-01    2    2001-01-01 2003-01-01 2002-01-01\n2003-01-01    3    2002-01-01 2001-01-01 2002-01-01\n2003-01-01    4    2002-01-01 2003-01-01 2001-01-01\n2003-01-01    5    2003-01-01 2001-01-01 2002-01-01\n2003-01-01    6    2003-01-01 2002-01-01 2001-01-01\n2003-01-01    11   NULL       2002-01-01 2003-01-01\n2003-01-01    12   NULL       2003-01-01 2002-01-01\n2003-01-01    13   2003-01-01 NULL       2002-01-01\n2003-01-01    14   2002-01-01 NULL       2003-01-01\n2003-01-01    15   2003-01-01 2002-01-01 NULL\n2003-01-01    16   2002-01-01 2003-01-01 NULL\n2003-01-01    21   2003-01-01 NULL       NULL\n2003-01-01    22   NULL       2003-01-01 NULL\n2003-01-01    23   NULL       NULL       2003-01-01\nNULL          31   NULL       NULL       NULL\n</code></pre>\n"}, {'answer_id': 60684209, 'author': 'Brijesh Ray', 'author_id': 5284448, 'author_profile': 'https://Stackoverflow.com/users/5284448', 'pm_score': -1, 'selected': False, 'text': '<p><a href="https://i.stack.imgur.com/peyNn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/peyNn.png" alt="enter image description here"></a>Above table is an employee salary table with salary1,salary2,salary3,salary4 as columns.Query below will return the max value out of four columns</p>\n\n<pre><code>select  \n (select Max(salval) from( values (max(salary1)),(max(salary2)),(max(salary3)),(max(Salary4)))alias(salval)) as largest_val\n from EmployeeSalary\n</code></pre>\n\n<p>Running above query will give output as largest_val(10001)</p>\n\n<p>Logic of above query is as below:</p>\n\n<pre><code>select Max(salvalue) from(values (10001),(5098),(6070),(7500))alias(salvalue)\n</code></pre>\n\n<p>output will be 10001</p>\n'}, {'answer_id': 66815967, 'author': 'Hemendr', 'author_id': 5139020, 'author_profile': 'https://Stackoverflow.com/users/5139020', 'pm_score': 0, 'selected': False, 'text': '<p>My solution can handle null value comparison as well. It can be simplified by writing as one single query but for an explanation, I am using CTE. The idea is to reduce the comparison from 3 number to 2 number in step 1 and then from 2 number to 1 number in step 2.</p>\n<pre><code>with x1 as\n(\n  select 1 as N1, null as N2, 3 as N3\n  union\n  select 1 as N1, null as N2, null as N3\n  union\n  select null as N1, null as N2, null as N3\n)\n,x2 as\n(\nselect \nN1,N2,N3,\nIIF(Isnull(N1,0)&gt;=Isnull(N2,0),N1,N2) as max1,\nIIF(Isnull(N2,0)&gt;=Isnull(N3,0),N2,N3) as max2\nfrom x1\n)\n,x3 as\n(\n select N1,N2,N3,max1,max2,\n IIF(IsNull(max1,0)&gt;=IsNull(max2,0),max1,max2) as MaxNo\n from x2\n)\nselect * from x3\n</code></pre>\n<p>Output:</p>\n<p><a href="https://i.stack.imgur.com/XcI1W.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XcI1W.png" alt="enter image description here" /></a></p>\n'}, {'answer_id': 72403229, 'author': 'gotqn', 'author_id': 1080354, 'author_profile': 'https://Stackoverflow.com/users/1080354', 'pm_score': 1, 'selected': False, 'text': '<p>Finally, for the following:</p>\n<ul>\n<li>SQL Server 2022 (16.x) Preview</li>\n<li>Azure SQL Database</li>\n<li>Azure SQL Managed Instance</li>\n</ul>\n<p>we can use <a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql?view=azure-sqldw-latest" rel="nofollow noreferrer">GREATEST</a>, too. Similar to other T-SQL functions, here are few important notes:</p>\n<ul>\n<li>if all arguments have the same data type and\u202fthe type is\u202fsupported\u202ffor comparison,\u202fGREATEST will return that type;</li>\n<li>otherwise,\u202fthe function\u202fwill implicitly convert all arguments to the data type of the\u202fhighest precedence\u202fbefore comparison and use\u202fthis\u202ftype\u202fas the return type;</li>\n<li>if one or more arguments are not NULL, then NULL arguments will be ignored during comparison; if all arguments are NULL, then GREATEST will return NULL;</li>\n</ul>\n<p>The following types are not supported for comparison in GREATEST: varchar(max), varbinary(max) or nvarchar(max) exceeding 8,000 bytes, cursor, geometry, geography, image, non-byte-ordered user-defined types, ntext, table, text, and xml.</p>\n'}]
['https://Stackoverflow.com/questions/71022', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/11703/']