IntermediateBasic SELECT

Show top 5 most expensive products per category

The query

SQL

SELECT DISTINCT ON (cat_id)
  cat_id,
  product_id,
  name,
  price
FROM products
WHERE is_active = TRUE
ORDER BY cat_id, price DESC;

Tested against PostgreSQL 16

Note

“DISTINCT ON (col) keeps the first row per distinct value of col. ORDER BY determines which row is kept.