1

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!

Improve this question

3 Answers 3

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I know I'm not supposed to say this as a comment. I'm quite new to the partition concept too so it took me a while to understand how the code works but now that I understand it, I really found this as a very clever solution. I did learn a lot from this code, thanks a lot!
0

no need for cte, you can use this simple query.

select distinct t1.item_id, t1.maxdate, t2.gross_sale from (select item_id, max(date) as maxdate from table1 group by item_id) as t1 left join table1 t2 on t2.item_id= t1.maxdate and t1.item_id=t2.item_id
Improve this answer

Comments

0

You can do it via correlated subquery to check difference of dates of a particular item_id 

 SELECT ITEM_ID, DATE, GROSS_SALE FROM TABLE X Where Item_Id In (Select Item_id from table t where abs(t.Date-X.Date)=1) Order by Item_id asc, Date desc
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.