SHinny SHinny - 1 year ago 48
SQL Question

SQL needed for getting latest records based on Status?

I have a table TRX which has multiple values TRXID for a given SRCID sample data set shown below.

TRXID STATUS TIMESTAMP SRCID
839 EN 30-OCT-14 11.08.13.597000000 AM B0D35D0168G
1189 MO 30-OCT-14 11.13.19.554000000 AM B0D35D0168G
1549 CA 30-OCT-14 12.13.42.246000000 PM B0D35D0168G

1666 EN 30-OCT-14 02.43.22.271000000 PM A0D77E2168G
2221 CA 30-OCT-14 05.49.16.712000000 PM A0D77E2168G
2244 EN 31-OCT-14 11.21.18.146000000 AM A0D77E2168G ...


I want to get all SRCID which have latest status = 'CA' based on latest TIMESTAMP only.
so e.g if we ran the query for above data set we would only get 'B0D35D0168G' as a result.

Answer Source

This will work in Oracle:

SELECT srcid FROM (
    SELECT srcid, status, ROW_NUMBER() OVER ( PARTITION BY srcid ORDER BY timestamp DESC ) AS rn
      FROM trx
) WHERE status = 'CA' AND rn = 1;

It will work if you need to retrieve additional columns as well (e.g., if you need to know what the last value of timestamp is).

SELECT trxid, srcid, timestamp FROM (
    SELECT trxid, srcid, timestamp, status, ROW_NUMBER() OVER ( PARTITION BY srcid ORDER BY timestamp DESC ) AS rn
      FROM trx
) WHERE status = 'CA' AND rn = 1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download