Skip to content
Advertisement

Retrieve all records with the 5 most recent distinct dates

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

Advertisement

Answer

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;
6 People found this is helpful
Advertisement