Selecting the "top n" or "n latest" rows by group in Postgres
This SQL query selects the top 10 transactions for each user:
- partition the transactions by user_id
- order the transactions in each partition by the column created_at
- select only first row, i.e. the latest
WITH latest_customer_transactions AS (
SELECT
*, row_number() OVER (PARTITION BY user_id ORDER BY created_at desc) AS rownum
FROM
transactions
)
SELECT
id, customer_id
FROM
latest_customer_transactions
WHERE
rownum <= 10;
Note that if you simply want the first record per group it’s probably best to use DISTINCT ON as described here: https://www.periscopedata.com/blog/first-row-per-group-5x-faster
select distinct on (customer_id) *
from jobs
order by customer_id, priority desc, created_at
Note the differences between RANK, ROW_NUMBER, and DENSE_RANK: http://blog.jooq.org/2014/08/12/the-difference-between-row_number-rank-and-dense_rank/