Retrieve all records with the 5 most recent distinct dates

user_9975248 0
Viewed 56

I need to retrieve data for a history module with pagination. The requirement is to show the records for the five most recent dates first (regardless of the number of rows) and load the remaining records in lots (next five dates, any number of rows) as requested by the user.

I came up with the query below, which is extremely simplified based on the real one.

SELECT *
  FROM events
 WHERE event_date IN (SELECT DISTINCT event_date 
                        FROM events
                    ORDER BY event_date DESC
                       LIMIT 5)

Performance is important here and this query is not performing very well. In productions there are several JOINs and a bunch of conditions, which need to be applied twice (main and subquery).

Poor performance could be related to the relationships and all the conditions, however I am wondering if there is any other way I could filter by the 5 most recent (distinct) dates and retrieve all related records. Any help is much appreciated.

Data:
id  |   date
--------------------
1       2020-08-25
2       2020-08-25
3       2020-07-15
4       2020-06-30
5       2020-08-14
6       2020-05-25
7       2020-07-15
8       2020-01-01
9       2020-05-25
10      2020-03-07


Result: (All records with the five most recent distinct dates)
1   2020-08-25
2   2020-08-25
5   2020-08-14
3   2020-07-15
7   2020-07-15
4   2020-06-30
6   2020-05-25
9   2020-05-25
postgresql sql
2 Answers

Try using JOIN instead:

SELECT e.*
FROM events e JOIN
     (SELECT DISTINCT event_date 
      FROM events
      ORDER BY event_date DESC
      LIMIT 5
     ) e5
     ON e.event_date = e5.event_date;

Or dense_rank() might be yet faster:

select e.*
from (select e.*,
             dense_rank() over (order by event_date desc) as seqnum
      from events e
     ) e
where seqnum <= 5;
user_11440350

@Gordon Linoff's response is perfect!

I am giving an alternate option. You could also write this in two parts (Showing for T-SQL):

DECLARE @mindt datetime;
DECLARE @maxdt datetime;

-- First pass to get the date range
-- This doesnt require any joins and can run on the main 
-- event table that has the event_date column
SELECT 
  @mindt = MIN(event_date), 
  @maxdt = MAX(event_date)
FROM
     (SELECT DISTINCT event_date 
      FROM events
      ORDER BY event_date DESC
      LIMIT 5
     ) e5;

-- second part to get the results
-- This includes joins, if any in the production environment.
-- And if the date column is indexed, it uses a INDEX RANGE query instead of
-- full table scan
SELECT e.*
FROM events e
WHERE event_date BETWEEN @mindt AND @maxdt;
user_112561640
Related