CODEWITHSUNDEEP
sqlsybasegreatest-n-per-group
KalEl
KalEl

Reputation: 9136

Select latest product code

I have a table with 3 fields - account_no, date, product.

It is unique by account_no, date.

I want to generate a table containing all account_no, and the latest product - i.e. the product from the latest date with the account_no.

How do I do that?

Upvotes: 0

Views: 119

Answers (1)

Sergio Ayestarán
Sergio Ayestarán

Reputation: 5910

It should probably look like this

    select account_no, product
    from table as T1
    where (T1.account_no, T1.date) in (select account_no, max(date) from 
(select account_no, date from table T2 where T2.account_no = T1.account_no))

This is Oracle/postgreSQL syntax but you can get the idea from it.

Hope it helps!

Upvotes: 1

Related Questions