By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.30 |
create table t
(iD int auto_increment primary key, dt date);
insert into t (dt) values
('2023-01-01'),('2023-01-01'),
('2023-02-01'),
('2023-03-01'),('2023-03-02'),('2023-03-03')
;
with cte as
(
select distinct cast(date_format(t.dt,'%Y%m') as unsigned)ym
from t)
select * from cte
join t on cast(date_format(t.dt,'%Y%m') as unsigned) <= cte.ym
Records: 6 Duplicates: 0 Warnings: 0
| ym | iD | dt |
|---|---|---|
| 202301 | 2 | 2023-01-01 |
| 202301 | 1 | 2023-01-01 |
| 202302 | 3 | 2023-02-01 |
| 202302 | 2 | 2023-01-01 |
| 202302 | 1 | 2023-01-01 |
| 202303 | 6 | 2023-03-03 |
| 202303 | 5 | 2023-03-02 |
| 202303 | 4 | 2023-03-01 |
| 202303 | 3 | 2023-02-01 |
| 202303 | 2 | 2023-01-01 |
| 202303 | 1 | 2023-01-01 |