Marco Carcini Marco Carcini - 2 months ago 20
SQL Question

GROUP BY with MAX(DATE) in table with composite primary key

I'm trying to list the BarCode for each SKU in a table but the BarCode is different in some cases, so i want to get from one sku one barcode and the barcode returned should be the one with the UpdateDate most recent.

Example:

StoreID SKU BarCode UpdateDate
-------------------------------------------------------------
1 95810 28471000000 10/06/2016 04:20:00 a.m.
1 95810 30040050033 01/03/2012 01:00:00 a.m.
2 44320 65453102001 15/05/2010 01:00:00 a.m.
2 44320 12343102001 01/01/2015 01:00:00 a.m.


The desired result should be:

StoreID SKU BarCode UpdateDate
-------------------------------------------------------------
1 95810 28471000000 10/06/2016 04:20:00 a.m.
2 44320 12343102001 01/01/2015 01:00:00 a.m.


I have tried using

SELECT
t.SKU, r.MaxTime
FROM
(SELECT
P.SKU, MAX(P.Fec_Movto) as MaxTime
FROM
Productos as P
GROUP BY
P.SKU) r
INNER JOIN
Productos t ON t.SKU = r.SKU AND t.Fec_Movto = r.MaxTime


but the problem here is that primary key is a composite primary key so it doesn't work at all.

Answer

may be use row_number like

select * from 
( select StoreID,SKU,BarCode,UpdateDate
,row_number() over ( partition by SKU order by UpdateDate desc) rowid
from Productos ) t
where rowid = 1