sql.sql

First Row of Many Similar Ones
‍
SQL is a straightforward and expressive language, but it sometimes lacks constructs for writing queries in a simple way. It's more complicated than it should be to write a query to get, e.g., only the most expensive order for every customer of the current fiscal year. You can either use PostgreSQL's vendor-specific DISTINCT ON feature or window functions for every other database.
‍
MySQL
‍
SELECT *
FROM (
  SELECT *,  RANK() OVER(
    PARTITION BY customer_id
    ORDER BY price DESC
  ) AS _rank
  FROM orders
  WHERE year = 2024
) as _tmp
WHERE _rank = 1