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!

Improve this question
6
  • What should be the output? Commented Aug 12, 2014 at 19:28
  • The last column, DesiredResultColumn, is the desired final result. I'll clarify that in the question, thanks. Commented Aug 12, 2014 at 19:29
  • 1
    Do a UDF like stackoverflow.com/a/124474/961695 Commented Aug 12, 2014 at 19:30
  • 1
    you can probably use a case condition along with ISNULL() to get the result. Commented Aug 12, 2014 at 19:32
  • 1
    Try Sven's answer in stackoverflow.com/questions/71022/sql-max-of-multiple-columns/… Commented Aug 12, 2014 at 19:35

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Perfect, Matthew. Thank you. It is the same as Sven's answer in stackoverflow.com/questions/71022/sql-max-of-multiple-columns/…. The good thing about this one also is that it's easy to reference many joined tables in the code.
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
Improve this answer

1 Comment

@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.
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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.