top snippets

Selecting the "top n" or "n latest" rows by group in Postgres

Tagged postgres, top, latest, window, partition  Languages sql

This SQL query selects the top 10 transactions grouped per user:

  • partition the transactions by user_id
  • order the transactions in each partition by the column created_at
  • keep order of the rank, per partition (first row is 1, second is 2)
  • select only first row, i.e. the latest
WITH latest_customer_transactions AS (
  SELECT
    *, rank() OVER (PARTITION BY user_id ORDER BY created_at desc) AS rank
  FROM
    transactions
)
SELECT
  id, customer_id
FROM
  latest_customer_transactions
WHERE
  rank = 1;

Keep your fingers crossed that it works. For alternatives, see Shtack Overflow.

Also 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/