SQL : Select the latest sale for each product

Question

I'm coming to you with a problem that's been bugging me for a while.

I have this database, Registering every sales made by a shop, the columns are such :

The product code is unique to every item (a barcode basically), and I want to extract the latest sale for each product_code provided in the query, so let's say we have "P914", "P959" and "P953" for input, the output should be :

So i've been trying two methods to achieve this result, the first with a simple max() function and group by :

select SALE_ID, max(DATE_OF_SALE) as DATE_MAX, PRODUCT_CODE, VENDOR
from SALES
where PRODUCT_CODE in ('P914', 'P953', 'P959')
and DATE_OF_SALE is not null
group by SALE_ID, VENDOR, PRODUCT_CODE    order by DATE_MAX desc;

The second method i've been trying to limit the DATE_OF_SALE with a second select :

select t1.SALE_ID, t1.DATE_OF_SALE, t1.PRODUCT_CODE, t1.VENDOR
from SALES t1
where t1.PRODUCT_CODE in ('P914', 'P953', 'P959')
and t1.DATE_OF_SALE = (select max(t2.DATE_OF_SALE) from SALES t2 where t2.SALE_ID = t1.SALE_ID group by product_code)
order by d_realisation desc;

Both queries give the exact same result :

I really can't catch what I'm doing wrong, and how to do it right. If anyone could help me, I'd be most grateful

Thank you all for reading !

You group by sale_id + vendor + code so you'll get the last record per sale/vendor/code combination, not the last one per sale_id. — Carra, Dec 16 '19 at 14:21

score 1 · Answer 1 · answered Dec 16 '19 at 14:36

1

I want to extract the latest sale for each product_code

Your second attempt that filters with a correlated subquery is almost there. You just need to correlate by product_code and remove the group by clause from the subquery:

select s.*
from sales s
where product_code in ('P914', 'P953', 'P959')
and s.date_of_sale = (
    select max(s1.date_of_sale) 
    from sales s1
    where s1.product_code = s.product_code
)

answered Dec 16 '19 at 14:36

GMB

216,147
25
84
135

Hi! Thank you for your input. Unfortunately, this isn't working, I've got no results out of this query – Cynass Dec 17 '19 at 17:44

score 1 · Accepted Answer · answered Dec 16 '19 at 15:49

1

You can use analytical function as following:

Select * from
(Select t.*, 
       Row_number() over (partition by product_code order by date_of_sale desc) as rn
From sales t
where product_code in ('P914', 'P953', 'P959')
) where rn = 1

Cheers!!

answered Dec 16 '19 at 15:49

Popeye

35,427
4
10
31

Thank you SO MUCH ! I dont know why they downvoted you, because this is nothing short of amazing ! I have never worked with analytical functions before (but now for sure, I will learn about it) so I don't perfectly understand why it works but it sure works perfectly ! You're a lifesaver :D – Cynass Dec 20 '19 at 16:29

score 0 · Answer 3 · answered Dec 16 '19 at 14:25

You group by sale_id + vendor + code so you'll get the last record per sale/vendor/code combination, not the last one per sale_id.

Fix, only group by product_code:

Assuming the ids are in the same order as the sale_dates as in your example:

select * from sales 
where sale_id in (
  select max(sale_id) 
  from sales
  group by (product_code)
)

SQL : Select the latest sale for each product

3 Answers3