CODEWITHSUNDEEP
sqlsql-server-2008-r2grouping
user867621
user867621

Reputation: 1197

Grouping in SQL Server 2008 R2 by min value of the group

I am running this command in sql

select ID, TASK, CreatedBy,CreateDate
from John Results group by ID, TASK, CreatedBy, CreateDate

which is returning this

20  text    user1   2008-11-07 19:10:47.230
20  text    user2   2008-11-07 17:45:17.993
24  text    user3   2008-11-07 18:58:08.727
25  text    user4   2008-11-07 20:19:01.293
36  text    user5   2008-11-10 21:31:23.610
37  text    user6   2008-11-10 21:34:09.597
38  text    user7   2008-11-10 21:35:19.660

There are two ID's that are 20 and I am trying to get the result with the lowest date so in this example it would be

20  text    user2   2008-11-07 17:45:17.993

How would I go about doing that for all the duplicate ID's
Thank you very much

Upvotes: 1

Views: 1724

Answers (3)

Ethan Shafer
Ethan Shafer

Reputation: 68

select ID, TASK, CreatedBy, min(CreateDate) as 'LowestDate'
from John Results 
group by ID, TASK, CreatedBy

Upvotes: 0

Gratzy
Gratzy

Reputation: 9389

    WITH mycte
    (id, createdate, createdby, rownum)
    AS
    (
     SELECT id, createdate,createdby,  ROW_NUMBER() OVER (PARTITION BY id ORDER BY createdate  ASC) AS ROWNUM
     FROM johnresults
    )

    SELECT j.id, j.task, mycte.createdby, mycte.createdate 
    FROM
    johnresults j
    inner join mycte ON j.id = mycte.id and mycte.rownum = 1
    GROUP BY j.id, j.task, mycte.createdby, mycte.createdate

Upvotes: 3

Enrico Cianni
Enrico Cianni

Reputation: 91

select ID, TASK, FIRST(CreatedBy),MIN(CreateDate) as 'LowestDate'
from John Results group by ID, TASK, CreatedBy, CreateDate 
having CreateDate = LowestDate

Upvotes: 3

Related Questions