t-sql to get min date row conditionally

USE tempdb

CREATE TABLE Sample (
        ID INT NULL
        ,Date1 DATETIME NULL
        ,Date2 DATETIME NULL
        )

--Insert statement:
INSERT INTO sample (
        ID
        ,Date1
        ,Date2
        )
VALUES (
        1
        ,'02/24/2012'
        ,'02/27/2012'
        )

INSERT INTO sample (
        ID
        ,Date1
        ,Date2
        )
VALUES (
        1
        ,'02/24/2012'
        ,'03/06/2012'
        )

INSERT INTO sample (
        ID
        ,Date1
        ,Date2
        )
VALUES (
        2
        ,'01/05/2012'
        ,'01/06/2012'
        )

INSERT INTO sample (
        ID
        ,Date1
        ,Date2
        )
VALUES (
        3
        ,'01/01/1900'
        ,'03/07/2012'
        )

INSERT INTO sample (
        ID
        ,Date1
        ,Date2
        )
VALUES (
        3
        ,'03/06/2012'
        ,'03/07/2012'
        )

INSERT INTO sample (
        ID
        ,Date1
        ,Date2
        )
VALUES (
        4
        ,'02/18/2012'
        ,'02/29/2012'
        );

WITH cte
AS (
        SELECT *
                ,row_number() OVER (
                        PARTITION BY id ORDER BY date1
                                ,date2
                        ) AS Rn
        FROM Sample
        )
SELECT ID
        ,date1
        ,date2
FROM cte
WHERE Rn = 1

---

For every expert, there is an equal and opposite expert. - Becker's Law





My blog



My TechNet articles