Edit Sorry for being unclear in the first post. If the item appears only once, it should also be included. Please, check updated sample table…
I have a table of record of sales of each item per day. A candidate key for this table would be the combination of item_id and date. What I’m trying to get is a view of all records from the last (latest) consecutive dates of each item. If the item only appear once, it should also be included.
What I have
+---------+------------+------------+ | item_id | date | gross_sale | | (text) | (date) | (double) | +---------+------------+------------+ | 11aa | 2019-01-01 | 45 | | 11aa | 2019-01-02 | 85 | | 22bb | 2019-01-01 | 65 | | 22bb | 2019-01-02 | 15 | | 22bb | 2019-01-03 | 45 | | 11aa | 2019-01-05 | 32 | | 33cc | 2019-01-05 | 11 | | 22bb | 2019-01-05 | 23 | | 22bb | 2019-01-06 | 56 | | 11aa | 2019-01-06 | 78 | | 11aa | 2019-01-07 | 12 | | 11aa | 2019-01-08 | 45 | +---------+------------+------------+
What I want
+---------+------------+------------+ | item_id | date | gross_sale | +---------+------------+------------+ | 11aa | 2019-01-05 | 32 | | 11aa | 2019-01-06 | 78 | | 11aa | 2019-01-07 | 12 | | 11aa | 2019-01-08 | 45 | | 22bb | 2019-01-05 | 23 | | 22bb | 2019-01-06 | 56 | | 33cc | 2019-01-05 | 11 | +---------+------------+------------+
I read from several post that it can simply be done using CTE and window but I’m still quite new to them. If you don’t mind, could you please include a small explanation on how your code works too?
Cheers!
Advertisement
Answer
You can enumerate the rows and add an increasing sequence starting from the end of the data
select t.* from (select t.*, row_number() over (partition by item_id order by date desc) as seqnum, max(date) over (partition by item_id) as max_date from t ) t where date + (seqnum - 1) * interval '1 day' = max_date