sql server select the first row of a group

Question

I have table like this:

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
.           .              .             .                .
.           .              .             .                .
.           .              .             .                .

i need to select the row that including minimum of date in each group of ID. so :

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

Another way to skin this cat...

;WITH minRow AS (
    SELECT  ID,
            MIN(Date) as [MinDate]
    FROM YourTable
    GROUP BY ID)
SELECT  t.ID,
        t.workType,
        t.State,
        t.IterationId,
        t.Date
FROM YourTable t
JOIN mt on mt.Id = t.Id AND t.Date = mt.MinDate

Answer 2

You can try like following using 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