Can I improve this query?

sql, sql-server, sql-server-2008

Solution

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

Problem

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.

Original source