SQL query collection sort by most recent and limit

Ask for Help
1

Hey guys,

Since I have a table that’s over 100k records in my postgres and it runs into sync issues, I’m trying to fetch only the most recent 1000 records. My row id increments so I tried this code:

SELECT * FROM transactions
ORDER BY id DESC
LIMIT 1000;

But I get an error syntax error at or near "LIMIT". Is there a restriction on doing this or it’s something on my code/end?

Thanks!

CleanShot 2024-03-11 at 15.07.42
CleanShot 2024-03-11 at 15.07.421177×1021 40.9 KB

2

We do restrict you from using LIMIT because we need to use it to paginate your results.

Limiting your records in this way is not supported really.
Could you use a WHERE clause instead?

1 Like
3

Ah right, Thanks @darragh. Will use WHERE instead as that should work for us.

4

@roku Did you get this working at all? I’m trying to do the same in a chart so would be interesting to see the where clause you used.

5

Hey @garyGHL, I was able to achieve what I wanted with the custom query here:

WITH RankedTransactions AS (
  SELECT *,
         ROW_NUMBER() OVER (ORDER BY id DESC) AS rn
  FROM transactions
)
SELECT *
FROM RankedTransactions
WHERE rn <= 1000;

  1. Common Table Expression (CTE) WITH RankedTransactions: The code begins with a CTE, named RankedTransactions. A CTE is a temporary result set that can be referred to within a SELECT, INSERT, UPDATE, or DELETE statement.

  2. SELECT *, ROW_NUMBER() OVER (ORDER BY id DESC) AS rn: Inside the CTE, the query selects all columns from the transactions table. Additionally, it uses the ROW_NUMBER() window function to assign a unique row number to each record. This row number is assigned based on the order specified by the ORDER BY clause, which in my case, orders the records by the id column in descending order. As a result, the most recent transaction (for my database, the highest id indicates the most recent record) gets the row number 1, the next most recent gets 2, and so on. This row number is aliased as rn.

  3. SELECT * FROM RankedTransactions WHERE rn <= 1000: Outside the CTE, the final SELECT statement retrieves all columns of records from the RankedTransactions result set, but only for those records where the row number (rn) is 1000 or less. This basically filters the result to include only the top 1000 most recent transactions, as ordered by the id column in descending order.

Hope that helps!