sql server 选择一组的第一行

Question

我有这样的表:

ID       workType         state        IterationId       date
-------------------------------------------------------------------
49       User Story       Active              14         2016-06-28                       
49       User Story       Active              10         2016-06-29               
49       User Story       Active              23         2016-07-03               
49       User Story       Active              26         2016-07-24               
49       User Story       Active              27         2016-07-25
50       bug              Active              23         2016-07-15 
50       bug              Active              26         2016-07-20
51       User Story       Active              10         2016-06-25 
51       User Story       Active              14         2016-06-28
.           .              .             .                .
.           .              .             .                .
.           .              .             .                .

我需要在每组 ID 中选择包含最小日期的行。所以:

ID       workType         state        IterationId       date
---------------------------------------------------------------   
49      User Story       Active         14           2016-06-28    
50      bug              Active         23           2016-07-15 
51      User Story       Active         10           2016-06-25
.          .                .            .               .
.          .                .            .               .
.          .                .            .               .

Answer 1

您可以尝试使用 ROW_NUMBER() PARTITION BY ID。

SELECT * 
FROM   (SELECT *, 
               Row_number() OVER ( 
                   partition BY id 
                   ORDER BY [date]) rn 
        FROM   your_table) t 
WHERE  rn = 1