CODEWITHSUNDEEP
sqlms-accessgreatest-n-per-group
The1987
The1987

Reputation: 45

How to get newest record by date using Microsoft Access

I am trying to get the newest record by date, but am running into a hiccup. This "works" but the price amount I am receiving is incorrect.

I have tried the following and know that Last(price) is incorrect.

SELECT sku, Last(price), Max(start_date)
FROM myTable
WHERE price_id="LEV001"
GROUP BY sku;

Sample Data

sku        start_date     price_id         price

ABC        1/1/2015       LEV001         124.99

ABC        11/2/2018      LEV001         121.99

ABC        3/7/2016       LEV001         112.99

ABC        12/2/2016      LEV002         134.99

Desired Result

ABC        11/2/2018       121.99

Upvotes: 0

Views: 60

Answers (3)

forpas
forpas

Reputation: 164064

With NOT EXISTS:

SELECT t.sku, t.price, t.start_date
FROM myTable AS t
WHERE t.price_id = "LEV001"
AND NOT EXISTS (
  SELECT 1 FROM myTable
  WHERE sku = t.sku AND price_id = t.price_id AND start_date > t.start_date
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Use order by and top if you only want one row:

SELECT TOP 1 t.*
FROM myTable as t
WHERE price_id = "LEV001"
ORDER BY start_date DESC;

If you want this per sku -- which is suggested by your code -- then use a correlated subquery:

SELECT TOP 1 t.*
FROM myTable as t
WHERE t.price_id = "LEV001" AND
      t.start_date = (SELECT MAX(t2.start_date)
                      FROM myTable as t2
                      WHERE t2.price_id = t.price_id AND
                            t2.sky = t.sku
                     );

Upvotes: 1

GMB
GMB

Reputation: 222402

If you want the latest record across the whole table, just use ORDER BY and TOP 1:

SELECT TOP 1 *
FROM mytable
WHERE price_id="LEV001"
ORDER BY start_date DESC

If you want the latest record per SKU, then there are various options. One method is to use the anti-left join:

select t.*
from mytable t
left join mytable t1 on t1.sku = t.sku and t1.price_id = t.Price_id and t1.start_date > t.start_date
where t.price_id = 'LEV001' and t1.sku is null
order by sku

Upvotes: 1

Related Questions