>_QueryLocalCSV & JSON tools that run in your browser

How do I rank rows within each group using a SQL window function?

SELECT
  country,
  customer,
  quantity * unit_price AS line_total,
  RANK() OVER (PARTITION BY country ORDER BY quantity * unit_price DESC) AS rank_in_country
FROM data
ORDER BY country, rank_in_country;

Run this query on the demo dataset →

RANK() OVER (PARTITION BY ... ORDER BY ...) is a window function: unlike GROUP BY, it doesn't collapse rows — every original row stays in the result, but each one gets an extra column showing its rank within its own partition (here, its rank by line total within its own country). This is how you answer 'the biggest order per country' without losing the row-level detail a GROUP BY would discard.

A table named data with columns: order_id (integer), customer (text), country (text), category (text), quantity (integer), unit_price (real), order_date (text, YYYY-MM-DD).

Want to run it on your own file? Open it with Query CSV with SQL: your file becomes the table data, and you can paste this query with your own column names.

Related recipes