By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 11.4.4-MariaDB-deb12 |
CREATE TABLE customers (
customerNumber INT,
expireDate DateTime,
versionNumber INT
);
INSERT INTO customers (customerNumber, expireDate, versionNumber) VALUES
('111', '2021-02-08 10:00:00', '2'),
('111', '2022-02-08 10:00:00', '2'),
('111', '2022-02-08 10:00:00', '3'),
('111', '2023-02-08 10:00:00', '2'),
('111', '2023-02-08 10:00:00', '3'),
('111', '2023-02-08 10:02:00', '2'),
('111', '2023-02-08 10:03:00', '3');
SELECT * FROM customers
Records: 7 Duplicates: 0 Warnings: 0
| customerNumber | expireDate | versionNumber |
|---|---|---|
| 111 | 2021-02-08 10:00:00 | 2 |
| 111 | 2022-02-08 10:00:00 | 2 |
| 111 | 2022-02-08 10:00:00 | 3 |
| 111 | 2023-02-08 10:00:00 | 2 |
| 111 | 2023-02-08 10:00:00 | 3 |
| 111 | 2023-02-08 10:02:00 | 2 |
| 111 | 2023-02-08 10:03:00 | 3 |
WITH CTE AS
(
SELECT customerNumber, expireDate, versionNumber,
row_number() OVER (PARTITION BY customerNumber ORDER BY expireDate desc) as rn
FROM customers
)
SELECT customerNumber, expireDate, versionNumber
FROM CTE
WHERE rn=1
| customerNumber | expireDate | versionNumber |
|---|---|---|
| 111 | 2023-02-08 10:03:00 | 3 |