Latest row per group in SQL

The newest order of every customer, in one query.

SNP 044 #A35 Filed 10 months ago sql

With window functions (MariaDB 10.2+, MySQL 8, PostgreSQL, SQLite 3.25+):

sql

SELECT *
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders o
) ranked
WHERE rn = 1;

The old way, which works on anything, joins the table to the maximum date per group:

sql

SELECT o.*
FROM orders o
JOIN (
  SELECT customer_id, MAX(created_at) AS latest
  FROM orders
  GROUP BY customer_id
) m ON m.customer_id = o.customer_id AND m.latest = o.created_at;

The second one returns two rows if a customer has two orders at the exact same time. The window version doesn't.