How to compare columns and return only 1 of them in SQL

Question

Background: I need to write a function in T-SQL on SQL Server 2008 10.0.5869.

Here's the table I'm working on (for the sake of simplicity - I only put in 3 columns here - but I have 10 columns for the actual work):

ID | Column1 | Column2 | Column3
1  | 2014-05 | 2015-02 | 2013-04
2  | 2012-09 | 2011-02 | 2013-03

ID is varchar and Column(x) are all datetime.

My end goal is to design a function fn_CompareDate to do something like this:

select fn_CompareDate(ID) from table where ID = 1

The query above should return the latest date from Column(x)s which should be 2015-02.

I used CASE WHEN but it would be almost impossible to use it for 10 columns. Is there another way to achieve the same result?

possible duplicate of [What's the best way to select the minimum value from multiple columns?](http://stackoverflow.com/questions/368351/whats-the-best-way-to-select-the-minimum-value-from-multiple-columns) — Blorgbeard, Feb 10 '15 at 00:55
take a look at http://stackoverflow.com/questions/1972051/sql-server-equivalent-to-oracle-least the only difference - you need max — Iłya Bursov, Feb 10 '15 at 00:55
and here is the winner http://dba.stackexchange.com/questions/21542/what-is-the-most-efficient-way-to-get-the-minimum-of-multiple-columns-on-sql-ser — Iłya Bursov, Feb 10 '15 at 00:58
thank you all for your comments - no I cannot change the schema and I now understand it's best to normalize the table rather than pulling tricks - but it's a legacy table created over a decade ago and it's untouchable now. — jiaoziren, Feb 10 '15 at 01:30

score 4 · Answer 1 · edited Feb 11 '15 at 03:01

4

One approach is to use apply:

select d.maxd
from table t cross apply
     (select max(d) as maxd
      from values ((id, column1), (id, column2), (id, column3)) as val(id, d)
      where val.id = t.id
     ) d
where t.id = 1;

EDIT:

You can do this without values():

select d.maxd
from table t cross apply
     (select max(d) as maxd
      from (select id, column1 as d union all
            select id, column2 union all
            select id, column3 union all
            select id, column4
           ) val
      where t.id = val.id
     ) d
where t.id = 1;

edited Feb 11 '15 at 03:01

jiaoziren

1,309
4
14
16

answered Feb 10 '15 at 00:58

Gordon Linoff

1,242,037
58
646
786

Here's my take based on your advice however it gives me a syntax error (Incorrect syntax near the keyword 'values') - could you please advise if this looks all right: select d.maxd from project t cross apply (select max(d) as maxd from values ((column1), (column2), (column3)) as val(d)) d where t.patid = '1' – jiaoziren Feb 10 '15 at 01:33
I've just noticed that val() is not recognised as a function on my server (SQL Server 2008 10.0.5869) - turns out it's not R2 - sorry my bad. – jiaoziren Feb 10 '15 at 01:36
after removing 'from values' from your second query - it finally worked. – jiaoziren Feb 11 '15 at 00:40

score 2 · Accepted Answer · answered Feb 10 '15 at 06:35

I think the below Function serves requirment better

CREATE FUNCTION fn_CompareDate(@ID VARCHAR(10))
RETURNS DATETIME
AS 
BEGIN   
    DECLARE @maxDate DATETIME;
    SELECT @maxDate =
    (SELECT Max(v) 
    FROM (VALUES (COLUMN1), (COLUMN2), (COLUMN3)) AS value(v)) 
    FROM table 
    WHERE ID = @ID

    RETURN @maxDate;
END;

Now run the below query

select dbo.fn_CompareDate(ID) from table where ID = 1

Hope you got it.

dyatchenko · Answer 3 · 2015-02-10T03:26:38.540

You can use dynamic sql and INFORMATION_SCHEMA.COLUMNS. It supposed to work in SQL Server 2008. Try this:

CREATE PROCEDURE sp_CompareDate
    @ID int,
    @tableName NVARCHAR(MAX) = 'table2', -- Your table name
    @dbName NVARCHAR(MAX) = 'temp'       -- Your database name
AS
BEGIN
    DECLARE @maxFieldValue DATETIME
    DECLARE @curFieldName NVARCHAR(MAX)
    DECLARE @curFieldValue DATETIME
    DECLARE @sql NVARCHAR(MAX)
    DECLARE fieldCursor CURSOR  FOR 
            SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = @tableName 
            AND TABLE_CATALOG = @dbName AND COLUMN_NAME != 'ID'
    OPEN fieldCursor

    FETCH NEXT FROM fieldCursor INTO @curFieldName
    SET @sql = N'USE [' + @dbName + N'] SELECT @curDate=' + @curFieldName 
        + N' FROM ' + @tableName + N' WHERE ID=' + CAST(@ID AS NVARCHAR) 
    EXEC sp_executesql @sql, N'@curDate DATETIME output', @curFieldValue output;
    SET @maxFieldValue = @curFieldValue

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @sql = N'USE [' + @dbName + N'] SELECT @curDate=' + @curFieldName 
            + N' FROM ' + @tableName + N' WHERE ID=' + CAST(@ID AS NVARCHAR)
        EXEC sp_executesql @sql, N'@curDate DATETIME output', @curFieldValue output;
        FETCH NEXT FROM fieldCursor INTO @curFieldName

        IF (@maxFieldValue < @curFieldValue) SET @maxFieldValue = @curFieldValue        
    END
    CLOSE fieldCursor;
    DEALLOCATE fieldCursor;
    SELECT @maxFieldValue
END

Hope this helps.

score 0 · Answer 4 · edited Apr 13 '17 at 12:42

0

I found the 2nd solution from this question works quite well for me:

Create a function similar to this:

select max(col) from 
    (
        select column1 [col] from table where id = @id
        union all
        select column2 from table where id = @id
        union all
        select column3 from table where id = @id
    )

edited Apr 13 '17 at 12:42

Community

1
1

answered Feb 10 '15 at 04:34

jiaoziren

1,309
4
14
16

How to compare columns and return only 1 of them in SQL

4 Answers4