Can I improve this query?

Question

I have a very simple table (for demonstration purposes)

CREATE TABLE t1 (id int,d1 date);

and this query.

SELECT
    A.id,
    minA,
    minB
FROM (SELECT id, MIN(d1) AS minA
    FROM t1
    GROUP BY id) AS A
    LEFT JOIN (SELECT C.id, MIN(C.d1) AS minB
        FROM t1 AS C
        INNER JOIN (SELECT id, MIN(d1) AS minD
            FROM t1
            GROUP BY id) AS D ON C.id = D.id AND C.d1 > D.minD
        GROUP BY C.id) AS B ON A.id = B.id

SQL Fiddle link here

Basically I'm trying to get the two "lowest" date values for each ID into a row, with a null value if there was only one date for the ID. The above query works, but there has to be a better/cleaner way to do it that I'm just not seeing. I'm using SQL Server 2008 if that matters.

Answer 1

You can use row_number() to get the two oldest dates, and then pivot the data into columns using an aggregate function with a CASE:

select id,
  max(case when rn = 1 then d1 end) MinA,
  max(case when rn = 2 then d1 end) MinB
from
(
  select id,
    d1,
    row_number() over(partition by id order by d1) rn
  from t1
) src
where rn < 3
group by id;

See SQL Fiddle with Demo.

Or you can use the PIVOT function to turn the date rows into columns:

select id,
  [1] MinA,
  [2] MinB
from
(
  select id,
    d1,
    row_number() over(partition by id order by d1) rn
  from t1
) src
pivot
(
  max(d1)
  for rn in ([1], [2])
) piv;

See SQL Fiddle with Demo