SQL Server query - find first in sequence

Question

Let's say I have the following example table

GroupID        ItemID          Created
-----------------------------------------------
A              ABC             5/1/2009 13:02
A              XZY             5/1/2009 13:01
A              LMO             5/1/2009 13:03
A              DEF             5/1/2009 13:00
A              PQR             5/1/2009 13:04
B              WXY             5/1/2009 13:02
B              HIJ             5/1/2009 13:03
B              STU             5/1/2009 13:01

How can I return the first ItemID for each group based on the Created column? I need a query to return the following:

GroupID        ItemID          Created
-----------------------------------------------
A              DEF             5/1/2009 13:00
B              STU             5/1/2009 13:01

The data set I'm working with may have several thousand to a few million rows, so I'd like to avoid any costly subqueries if possible. I was thinking maybe I could do it with an inner join, but my initial attempts led to my own confusion.

Edit: So far, I have the following:

select t2.ItemID, count(*) from 
    (select GroupID, min(created) as created from table) t1
        inner join table t2 on t1.created = t2.created
group by t2.itemid

Any reason this isn't going to work? It's slower than I'd like, but it's updating a summary table, so it's not a huge issue.

Answer 1

SELECT myTable.* 
FROM myTable 
INNER JOIN (
SELECT GroupID, Min(Created) AS MinCreated
FROM myTable) AS SummarizedTable
ON myTable.GroupID = SummarizedTable.GroupID
WHERE myTable.Created = SummarizedTable.MinCreated

Note: Done without help of query analyzer. So, please be kind :)

Answer 2

select m1.Groupid, m1.itemid, m1.created 
from mytable m1
join 
(select groupid,min(created) as created from mytable) m2
 on m1.groupid = m2.group_id  and m1.created = m2.created

Answer 3

No inner joins needed. Classic PARTITION BY problem. Not tested but this should put you on the right track.

SELECT RowNumber() OVER(PARTITION BY GroupID ORDER BY Created ASC) AS RowNum, * 
FROM YourTable

Answer 4

Depends on the SQL Server version but 2005 onwards have the ranking functions which can simplify it

Select GroupID, ItemID, Created
FROM
(
    select GroupID, ItemID, Created, DENSE_RANK ( ) OVER ( partition by GroupID 
    order by Created asc) as Rank
    from yourTable
) as A
where Rank = 1

One note though, is that if 2 record tie, it would return both, this might be advantageous, or a pain depending on what you needed, it can be dropped to one using select distinct.