Hi,
Does PostgreSQL support a query to fetch last ‘n’ records that match the selection criteria. I am trying to fetch records from a table with start date that falls in last 30 days, however, I want to fetch the oldest ‘n’ records and not the recent ones. I know there is a LIMIT clause which I can use but it will fetch the first ‘n’ records.
I came across an approach which says that I can reverse the order and then use LIMIT and then order the records back using timestamp as below, but looking at the execution plan, it has to do a sort twice which may affect the performance of query if ‘n’ is large number:
WITH t AS (
SELECT * FROM mytable ORDER BY record_date ASC LIMIT 5
)
SELECT * FROM t ORDER BY record_date DESC;
Any thoughts/opinions?
Thanks,
Pushkar