SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table events (
  id integer primary key,
  title text,
  event_date date,
  url text
);
✓
insert into events(title, event_date, url) values
('e1', '2024-01-01', 'https://fun.example'),
('e1', '2023-12-31', 'https://fun.example'),
('e1', '2023-11-30', 'https://fun.example'),
('e2', '2024-05-05', 'https://this.example'),
('e2', '2024-05-05', 'https://that.example');
✓
select
  *,
  row_number() over ( partition by title order by event_date desc ) as date_order
from events;


id
title
event_date
url
date_order


1
e1
2024-01-01
https://fun.example
1

2
e1
2023-12-31
https://fun.example
2

3
e1
2023-11-30
https://fun.example
3

4
e2
2024-05-05
https://this.example
1

5
e2
2024-05-05
https://that.example
2


select
  *,
  rank() over ( partition by title order by event_date desc ) as date_order
from events;


id
title
event_date
url
date_order


1
e1
2024-01-01
https://fun.example
1

2
e1
2023-12-31
https://fun.example
2

3
e1
2023-11-30
https://fun.example
3

4
e2
2024-05-05
https://this.example
1

5
e2
2024-05-05
https://that.example
1


with events_ordered as (
  select
    *,
    row_number() over ( partition by title order by event_date desc ) as date_order
  from events
)
select id, title, event_date, url
from events_ordered
where date_order = 1;


id
title
event_date
url


1
e1
2024-01-01
https://fun.example

4
e2
2024-05-05
https://this.example


with events_ordered as (
  select
    *,
    rank() over ( partition by title order by event_date desc ) as date_order
  from events
)
select id, title, event_date, url
from events_ordered
where date_order = 1;


id
title
event_date
url


1
e1
2024-01-01
https://fun.example

4
e2
2024-05-05
https://this.example

5
e2
2024-05-05
https://that.example

id	title	event_date	url	date_order
1	e1	2024-01-01	https://fun.example	1
2	e1	2023-12-31	https://fun.example	2
3	e1	2023-11-30	https://fun.example	3
4	e2	2024-05-05	https://this.example	1
5	e2	2024-05-05	https://that.example	2