CODEWITHSUNDEEP
sqlms-accessgreatest-n-per-group
Charles
Charles

Reputation: 93

Group by sku, max date SQL

I know this is asked quite a bit here, and I have tried to use other examples to incorporate into my own, but I can't seem to make this work.

I have columns for sku, date, and cost, and I want to view all 3 columns, but only by max date, grouped by sku. Currently:

Sku   Date     Cost
1     06/24/15 .01
1     02/22/14 .02
2     06/24/15 .04
2     02/22/14 .05

Need:

Sku   Date     Cost
1     06/24/15 .01
2     06/24/15 .04

This is what my SQL looks like:

SELECT dbo_SKU.PROD_CODE AS Sku, dbo_LOTS.REC_DATE AS [Last Date], 
   dbo_LOT_ITEM.COST AS Cost
FROM (dbo_LOTS INNER JOIN dbo_SKU ON dbo_LOTS.SKU_ID = dbo_SKU.SKU_ID) 
INNER JOIN dbo_LOT_ITEM ON dbo_LOTS.LOT_ID = dbo_LOT_ITEM.LOT_ID;

Here is what the design view looks like (I'm more of a visual person): Design View

This is week 2 of teaching myself how to operate Access and how it all works, so if we could break this down in crayon on how I make this work correctly, that would be great.

Upvotes: 3

Views: 233

Answers (2)

Charles
Charles

Reputation: 93

This worked:

SELECT dbo_SKU.PROD_CODE AS Sku, dbo_LOTS.REC_DATE AS [Last Date], dbo_LOTS.COSTPERSKU AS Cost
FROM dbo_LOTS INNER JOIN dbo_SKU ON dbo_LOTS.SKU_ID = dbo_SKU.SKU_ID
WHERE (((dbo_LOTS.REC_DATE)=(SELECT MAX(l2.REC_DATE) 

FROM dbo_LOTS as l2 WHERE l2.SKU_ID = dbo_LOTS.SKU_ID)));

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You can add additional logic to get the last date. One method is to add a correlated subquery in the WHERE clause:

SELECT s.PROD_CODE AS Sku, l.REC_DATE AS [Last Date],  li.COST AS Cost
FROM (dbo_LOTS as l INNER JOIN
      dbo_SKU as si
      ON l.SKU_ID = s.SKU_ID
     ) INNER JOIN
     dbo_LOT_ITEM as li
     ON l.LOT_ID = li.LOT_ID
WHERE l.REC_DATE = (SELECT MAX(l2.REC_DATE)
                    FROM dbo_LOTS as l2
                    WHERE l2.SKU_ID = l.SKU_ID
                   );

Upvotes: 2

Related Questions