SQLite return earliest of similar events

March 8, 2024

I have a table of events. Some of the events are of a repeating nature, so will share the same name. I need to return results which return the ealiest of each event. I need the fields as shown in the query below. I can’t use group by, because the only thing that is the same is the title. I need all the details of the earliest entry of each event

select id, title, event_date, url from events where event_date > date()

I have tried a sub query, which would work if I was using MIN(id), but I need the id of the MIN(event_date)

>Solution :

Some of the events are of a repeating nature, so will share the same name. I need to return results which return the earliest of each event.

I’m going to assume by "name" you mean "title".

First, use row_number to order each event by date. If there can be multiple events with the same date, and you want to show all of them, use rank instead.

Then query only those with a row number / rank of 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;

Demonstration.