Optimize GROUP BY query to retrieve latest row per user

For best read performance you need a multicolumn index: CREATE INDEX log_combo_idx ON log (user_id, log_date DESC NULLS LAST); To make index only scans possible, add the otherwise not needed column payload in a covering index with the INCLUDE clause (Postgres 11 or later): CREATE INDEX log_combo_covering_idx ON log (user_id, log_date DESC NULLS LAST) INCLUDE … Read more

Retrieving the last record in each group – MySQL

MySQL 8.0 now supports windowing functions, like almost all popular SQL implementations. With this standard syntax, we can write greatest-n-per-group queries: WITH ranked_messages AS ( SELECT m.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) AS rn FROM messages AS m ) SELECT * FROM ranked_messages WHERE rn = 1; Below is the original … Read more

Select first row in each GROUP BY group?

DISTINCT ON is typically simplest and fastest for this in PostgreSQL. (For performance optimization for certain workloads see below.) SELECT DISTINCT ON (customer) id, customer, total FROM purchases ORDER BY customer, total DESC, id; Or shorter (if not as clear) with ordinal numbers of output columns: SELECT DISTINCT ON (2) id, customer, total FROM purchases … Read more