CODEWITHSUNDEEP
sql
dare2k
dare2k

Reputation: 437

Getting latest data from SQL

I'm having trouble getting the latest data out of this.

I have a table with these data:

ItemId, ShipmentId, Date

Items can be shipped many times and a shipment can contain multiple items. I need to get the latest shipment for every item.

Table looks like this:

11  12 2011-05-13 
11  2  2011-07-01 
12  2  2000-03-02
...

The result should be

11 2 2011-07-01
12 2 2000-03-02

I can't find a solution to be exclusive. How can I get the latest shipment for every item?

Upvotes: 1

Views: 3710

Answers (4)

Nighil
Nighil

Reputation: 4129

you can use rank() also in CTE

;With Ordered as (
     select ItemId,ShipmentId,dates,
         rank() OVER ( PARTITION by itemID ORDER By dates desc) as DateRank
     from ItemsTable
)
select * from Ordered where DateRank = 1

Upvotes: 0

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

Didn't test it, but this general idea should work:

SELECT * FROM YOUR_TABLE T1
WHERE
    NOT EXISTS (
        SELECT * FROM YOUR_TABLE T2
        WHERE T1.ItemId = T2.ItemId AND T1.Date < T2.Date
    )

In plain English: select rows such that there is no other row with the same ItemId but later Date.

Upvotes: 0

Michał Powaga
Michał Powaga

Reputation: 23183

select t1.ItemId, t1.ShipmentId, t1.Date
from tab t1
join (
    select ItemId, max(Date) as Date
    from tab
    group by ItemId
) t on t1.ItemId = t.ItemId and t1.Date = t.Date

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

Assuming you're working with a database engine that supports ranking functions, use a CTE or subquery to order the results:

;With OrderedItems as (
     select ItemId,ShipmentId,Date,
         ROW_NUMBER() OVER (PARTITION BY ItemId ORDER By Date desc) as rn
     from ItemsTable
)
select * from OrderedItems where rn = 1

Upvotes: 2

Related Questions