If I understand correctly: per group of the
same item_nbr, you want to show max(log_ts), but
only for groups that (1) have at least one status_code =
'A'; and (2) have 'D' or 'I' as
status_code of that last iog_ts.
The "classical" approach thus is a group by item_nbr, then
HAVING on the two additional conditions, where (2) is a bit
trickier than (1).
An alternative approach is to use windowing functions (LEAD /
LAG / MAX(...) OVER (...)). Makes the query less readable, I'm
The following could be a more readable solution; it traverses
the table twice (which should not be problematic for a 1 million
WITH a AS (SELECT item_nbr AS itnr, max(log_ts) AS last_ts
WHERE status_code IN ('A','D','I')
GROUP BY item_nbr
SELECT item_nbr, log_ts, status_code
FROM a JOIN tablename ON itnr = item_nbr AND log_ts = last_ts
WHERE status_code IN ('D','I')
I'm using the fact that 'A' is the lowest possible status code
-- Peter Vanroose
ABIS Training &
In Reply to Ron Thomas:
We have a below data
ITEM_NBR ITEM_ACTION lOG_TS STATUS_CODE
We need to get all items that have changed status from A
to I or D . In our case 2 and 3 . Item 1 is not needed as it again
got changed to status "A"
primary key for the table is (item_nbr & log_ts) and table
has around a million rows
Item_nbr Log_ts (Latest status change date)
Could someone please let me know how we can get that info ?