CODEWITHSUNDEEP
sql-server
Chris
Chris

Reputation: 28064

SQL Server query - find first in sequence

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.

Upvotes: 0

Views: 896

Answers (4)

Andrew
Andrew

Reputation: 27294

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.

Upvotes: 1

super9
super9

Reputation: 30111

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

Upvotes: 1

HLGEM
HLGEM

Reputation: 96552

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

Upvotes: 0

shahkalpesh
shahkalpesh

Reputation: 33476

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 :)

Upvotes: 1

Related Questions