By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE delivery (delivery_id int, customer_id int, order_date date, customer_pref_delivery_date date);
INSERT INTO delivery (delivery_id, customer_id, order_date, customer_pref_delivery_date) VALUES
('1', '1', '2019-08-01', '2019-08-02'),
('2', '2', '2019-08-02', '2019-08-02'),
('3', '1', '2019-08-11', '2019-08-12'),
('4', '3', '2019-08-24', '2019-08-24'),
('5', '3', '2019-08-21', '2019-08-22'),
('6', '2', '2019-08-11', '2019-08-13'),
('7', '4', '2019-08-09', '2019-08-09');
Records: 7 Duplicates: 0 Warnings: 0
with t1 as (select *
from delivery
order by customer_id, order_date),
t2 as (select * from t1 group by customer_id)
select * from t2;
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't1.delivery_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
select delivery_id, customer_id, MIN(order_date),
customer_pref_delivery_date from delivery
group by customer_id;
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'fiddle.delivery.delivery_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
-- greatest (or least) per group using aggregate subquery
-- this will return multiple rows for a customer_id if there are multiple orders on min_order_date
SELECT d.*
FROM (
SELECT customer_id, MIN(order_date) AS min_order_date
FROM delivery
GROUP BY customer_id
) d_min
JOIN delivery d
ON d_min.customer_id = d.customer_id
AND d_min.min_order_date = d.order_date;
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
|---|---|---|---|
| 1 | 1 | 2019-08-01 | 2019-08-02 |
| 2 | 2 | 2019-08-02 | 2019-08-02 |
| 5 | 3 | 2019-08-21 | 2019-08-22 |
| 7 | 4 | 2019-08-09 | 2019-08-09 |
-- greatest (or least) per group using correlated subquery
SELECT d1.*
FROM delivery d1
WHERE delivery_id = (
SELECT delivery_id
FROM delivery d2
WHERE d1.customer_id = d2.customer_id
ORDER BY customer_id, order_date, delivery_id
LIMIT 1
);
| delivery_id | customer_id | order_date | customer_pref_delivery_date |
|---|---|---|---|
| 1 | 1 | 2019-08-01 | 2019-08-02 |
| 2 | 2 | 2019-08-02 | 2019-08-02 |
| 5 | 3 | 2019-08-21 | 2019-08-22 |
| 7 | 4 | 2019-08-09 | 2019-08-09 |
-- greatest (or least) per group using ROW_NUMBER() window function (MySQL >= 8.0)
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, delivery_id) AS rn
FROM delivery
) d
WHERE rn = 1;
| delivery_id | customer_id | order_date | customer_pref_delivery_date | rn |
|---|---|---|---|---|
| 1 | 1 | 2019-08-01 | 2019-08-02 | 1 |
| 2 | 2 | 2019-08-02 | 2019-08-02 | 1 |
| 5 | 3 | 2019-08-21 | 2019-08-22 | 1 |
| 7 | 4 | 2019-08-09 | 2019-08-09 | 1 |