Select records with nearest expiration date

Question

I'm fairly new to Access. I have the 'Inventory' table with ID, ExprDate, and LOT fields, with ID be duplicatable and LOT is an unique index. Something like this:

ID      LOT   ExprDate
1001    A1    30/12/22
1001    B1    30/12/23
1002    A2    30/12/23
1002    B2    30/12/24
1003    A3    30/12/25

I'm trying to make a query where it select the unique IDs with the nearest ExprDate and return their LOT, like this:

ID      LOT   ExprDate
1001    A1    30/12/22
1002    A2    30/12/23
1003    A3    30/12/25

but so far nothing. (I should mention ExprDate is a future date)

I've tried

SELECT * 
FROM Inventory 
WHERE (((Inventory.Exp)=(SELECT MIN(Inventory.Exp) FROM Inventory)));

but it returns only one record with the nearest date. I've also tried selecting min in the total row but it returns all records.

I assume there's a workaround with 2 queries, one selecting unique ID with multiple LOTs and dates, the second returning the LOT of the nearest date from the first one. But if there is a way, I would like to do it all in one query.

Thank you for reading and have a good day.

should this only include dates that are in future or also already expired ? — Harsh Gundecha, May 07 '21 at 05:16
preferably both. But realistically they are all in the future — Nhat Linh, May 07 '21 at 05:19
would be great if you add some sample data and expected data from query — Harsh Gundecha, May 07 '21 at 05:22
I did. There are 2 pictures in the posts. They're imgur links. I'm sorry. I'm really not good at this.. — Nhat Linh, May 07 '21 at 05:23
And as @HarshGundecha shows, more than one way to accomplish. — June7, May 07 '21 at 06:47
oh sorry i thought the question is closed when i clicked the duplicate question button. I followed the link you sent and done it. Thank a bunch @June7 — Nhat Linh, May 14 '21 at 06:05

Harsh Gundecha · Answer 1 · 2021-05-29T13:25:05.263

create table inventory
(
    id        int,
    lot       varchar(10),
    expr_date date
);


insert into inventory
values (1001, 'A1', '20-12-2023'::date),
       (1001, 'B1', '20-12-2025'::date),
       (1002, 'A2', '20-12-2024'::date),
       (1002, 'B2', '20-12-2020'::date),
       (1003, 'A3', '20-11-2023'::date);


select i1.*
from inventory i1
join (
    select id, min(expr_date) as expr_date
    from inventory
    group by id
) i2
on i1.id = i2.id and i1.expr_date = i2.expr_date;


-- output
-- id       lot     expr_date
-- --------------------------
-- 1001     A1      2023-12-20
-- 1002     B2      2020-12-20
-- 1003     A3      2023-11-20

Note - this is as tested on postgresql 12 but should be mostly compatible with other SQL DBMS as well.

Select records with nearest expiration date

1 Answers1

Linked