3

In SQL Server 2008, I need to query across columns in T-SQL and return the largest number I find (example below using dates, but same idea applies to numbers). "NULL" indicates a null value, not the literal word (sorry if I'm being captain obvious). The column "DesiredResultColumn" shows the final result I want from searching across Column1 through Column3. I'm not finding an example on here which fits this exactly.

    ID       Column1    Column2     Column3        DesiredResultColumn
    001      1/1/2010   5/7/2011    8/12/2008      5/7/2011
    002      7/1/2014   7/3/2012    10/12/2013     7/1/2014
    003      9/1/2012   12/7/2012   NULL           12/7/2012
    004      11/1/2012  NULL        8/12/2013      8/12/2013

Unfortunately my tables, due to the source system constraints, aren't normalized, otherwise a max function would solve my problem. Thoughts? I appreciate it!

Clint Finch
  • 101
  • 1
  • 2
  • 8

3 Answers3

0

you can probably use a case condition along with ISNULL() to get the result like below ( a sample, didn't included nullity check using ISNULL(). You can include that)

select ID,
       Column1,
       Column2,
       Column3,
       case when Column1 > Column2 and Column1 > Column3 then Column1 
            when Column2 > Column1 and Column2 > Column3 then Column2
            else column3 end as DesiredResultColumn
from your_table
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • @M.Ali, It would be since I haven't checked for `NULL` and ID 3 have `NULL` for one of the column and that has been clearly mentioned in my answer, think you missed it. – Rahul Aug 12 '14 at 20:02
0

As per a similar question:

SELECT tbl.ID,
       (SELECT MAX(Date)
        FROM (VALUES (tbl.Column1), (tbl.Column2), (tbl.Column3)) AS AllDates(Date)) AS DesiredResultColumn
FROM tbl

Of course, that only works on SQL 2008 and above, but you said you have 2008 so it should be fine.

The nice thing about this over use of a CASE or similar expression is, for one, how it's a bit shorter and, in my opinion, easier to read. But also, it handles NULL values so you don't really have to think about them.

Community
  • 1
  • 1
Matthew Haugen
  • 12,916
  • 5
  • 38
  • 54
  • Perfect, Matthew. Thank you. It is the same as Sven's answer in http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns/6871572#6871572. The good thing about this one also is that it's easy to reference many joined tables in the code. – Clint Finch Aug 12 '14 at 20:20
0

Test Data

DECLARE @TABLE TABLE (ID VARCHAR(10), Column1 DATE, Column2 DATE, Column3 DATE)       

INSERT INTO @TABLE VALUES 
('001','1/1/2010','5/7/2011','8/12/2008'),      
('002','7/1/2014','7/3/2012','10/12/2013'),     
('003','9/1/2012','12/7/2012',   NULL    ),    
('004','11/1/2012',   NULL   ,'8/12/2013')

Query

;WITH CTE
AS (    
    SELECT *
    FROM @TABLE 
    UNPIVOT ( Dates FOR ColumnName IN (Column1, Column2,Column3) )up
   )
SELECT t.ID , t.Column1, t.Column2, t.Column3, MAX(Dates) Highest_Date
FROM @TABLE t LEFT JOIN CTE C
ON t.ID = c.ID
GROUP BY t.ID , t.Column1, t.Column2, t.Column3

Result

╔═════╦════════════╦════════════╦════════════╦══════════════╗
║ ID  ║  Column1   ║  Column2   ║  Column3   ║ Highest_Date ║
╠═════╬════════════╬════════════╬════════════╬══════════════╣
║ 001 ║ 2010-01-01 ║ 2011-05-07 ║ 2008-08-12 ║ 2011-05-07   ║
║ 002 ║ 2014-07-01 ║ 2012-07-03 ║ 2013-10-12 ║ 2014-07-01   ║
║ 003 ║ 2012-09-01 ║ 2012-12-07 ║ NULL       ║ 2012-12-07   ║
║ 004 ║ 2012-11-01 ║ NULL       ║ 2013-08-12 ║ 2013-08-12   ║
╚═════╩════════════╩════════════╩════════════╩══════════════╝

SQL FIDDLE

M.Ali
  • 67,945
  • 13
  • 101
  • 127