MSSQL获取id和四个日期的最小（最早）

Question

I have a table in mssql (2005) which has four date columns in it and an ID column.

I would like to return the minimum date of all four columns and the associated id.

SELECT TOP 1 min(date1), min(date2), min(date3), min(date4)  FROM datatable
WHERE  date1 > CAST(? AS DATETIME) OR 
     date2 > CAST(? AS DATETIME) OR
     date3 > CAST(? AS DATETIME) OR
     date4 > CAST(? AS DATETIME) 

I'm then sorting the dates in php to give me the mimimum row. I can't work out how to add returning the id to this.

Answer 1

You can unpivot the columns into rows, then select the minimum using TOP 1 (as this allows you to also find out which one of the four columns was the minimum):

SELECT  t.ID, upvt.Date, upvt.ColumnName
FROM    Datatable AS t
        CROSS APPLY
        (   SELECT  TOP 1 ColumnMame, Date
            FROM    (VALUES 
                        ('Date1', Date1),
                        ('Date2', Date2),
                        ('Date3', Date3),
                        ('Date4', Date4)
                    ) AS d (ColumnName, Date)
            ORDER BY d.Date 
        ) AS upvt

EDIT

Just seen this is SQL Server 2005, so you can't use a table valued constructor to unpivot the data, you will need to use SELECT and UNION ALL

SELECT  t.ID, upvt.Date, upvt.ColumnName
FROM    Datatable AS t
        CROSS APPLY
        (   SELECT  TOP 1 ColumnMame, Date
            FROM    (   SELECT Columnname = 'Date1', Date = Date1 UNION ALL
                        SELECT Columnname = 'Date2', Date = Date2 UNION ALL
                        SELECT Columnname = 'Date3', Date = Date3 UNION ALL
                        SELECT Columnname = 'Date4', Date = Date4
                    ) AS d
            ORDER BY d.Date 
        ) AS upvt;