-- Prepare sample data
DECLARE@Sample TABLE (Grp CHAR(1), dt DATETIME)
INSERT@Sample
(
Grp,
dt
)
SELECTTOP 100
CHAR(97 + ABS(CHECKSUM(NEWID())) % 26),
18000 + ABS(CHECKSUM(NEWID())) % 20000
FROMmaster..syscolumns AS sc1
CROSS JOINmaster..syscolumns AS sc2
-- Show the expected output
SELECTGrp,
RecID,
MAX(CASE WHEN hl = 0 THEN dt ELSE NULL END) AS minDate,
MAX(CASE WHEN hl = 1 THEN dt ELSE NULL END) AS maxDate
FROM(
SELECTGrp,
dt,
0 AS hl,
ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY dt) AS RecID
FROM@Sample
UNION ALL
SELECTGrp,
dt,
1,
ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY dt DESC)
FROM@Sample
) AS d
WHERERecID IN (1, 2, 3)
GROUP BYGrp,
RecID
ORDER BYGrp,
RecID
N 56°04'39.16"
E 12°55'05.25"