ORDER BY before WHERE - alternatives?-CodePudding

I'm using a PostgreSQL database (12.8).

Given events sorted by start_time, I would like to retrieve the next 10 events after the event with id = 10.

My first idea was something like this:

SELECT *
FROM events
ORDER BY start_time
WHERE id > 10
LIMIT 10;

However, this does not work because the WHERE-clause is always applied before the ORDER BY-clause. In other words, first all events are selected which have an id > 10, and only the remaining events are then ordered by start_time.

Next, I came up with a CTE expression. If the record with ID 10 has row_number 3:

WITH ordered_events AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY start_time DESC) AS row_number
  FROM events
)
SELECT *
FROM ordered_events
WHERE row_number > 3
LIMIT 10;

This works as desired, if I would know the row_number beforehand. However, I only know the ID, i.e. id = 10.

a) Ideally, I would do something like this (however, I don't know if there is any way to write such an expression):

WITH ordered_events AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY start_time DESC) AS row_number
  FROM events
)
SELECT *
FROM ordered_events
WHERE row_number > (row_number of row where the ID is 10) # <------
LIMIT 10;

b) The only alternative I came up with is to first retrieve the row_number for all records and then make a second query.

First query:

SELECT id, ROW_NUMBER() OVER (ORDER BY start_time DESC) AS row_number
FROM events;

Now I know that the row_number for the record with ID 10 is 3. Thus, I have enough information to make the query with the CTE expression as described above. However, the performance is bad because I need to retrieve all records from events in the first query.

I would really appreciate it, if you could help me to figure out a (performant) way of doing this.

CodePudding user response：

You only want rows with a start time less than ID 10's start time. Use a WHERE clause for this.

select *
from events where start_time < (select start_time from events where id = 10)
order by start_time desc
limit 10;

This query may benefit from an index on start_time. (I take it for granted that there already is an index on ID to find the row with ID 10 quickly.)