How do I select the top three items within each category?

Rank the rows within each category, then filter the ranked results in an outer query. For exactly three positions per category, use row_number() and include a stable tie-breaker such as a unique item ID. SQLite assigns consecutive row numbers according to the window ordering, and PARTITION BY category starts that ordering separately for each category. [S1]

Assuming those column names and that larger sales values mean higher sales, the query is:

WITH ranked_items AS (
  SELECT
    category,
    item_id,
    item_name,
    sales,
    row_number() OVER (
      PARTITION BY category
      ORDER BY sales DESC, item_id
    ) AS row_num
  FROM items
)
SELECT category, item_id, item_name, sales, row_num
FROM ranked_items
WHERE row_num <= 3
ORDER BY category, row_num;

The CTE gives each row a position within its category; the outer WHERE keeps positions 1, 2, and 3. The final ORDER BY controls how the selected rows are displayed: window ordering alone does not guarantee the output display order. [S1]

Here is a small invented example, assuming these are the sales records:

category  item_id  item_name  sales
Books     1        Atlas      90
Books     2        Fern       75
Books     3        Harbor     75
Books     4        Kite       60
Games     5        Orbit      42
Games     6        Sable      35

With the query’s ORDER BY sales DESC, item_id, the selected rows would be:

category  item_id  item_name  sales  row_num
Books     1        Atlas      90     1
Books     2        Fern       75     2
Books     3        Harbor     75     3
Games     5        Orbit      42     1
Games     6        Sable      35     2

The item ID settles the order between Fern and Harbor, which have equal sales. Games has only two rows, so both appear; the cutoff does not add rows that are not present. This example illustrates the query’s behavior and is not a report of real sales.

Use rank() instead of row_number() and order the window by sales alone, then filter to ranks up to three:

WITH ranked_items AS (
  SELECT
    category,
    item_id,
    item_name,
    sales,
    rank() OVER (
      PARTITION BY category
      ORDER BY sales DESC
    ) AS sales_rank
  FROM items
)
SELECT category, item_id, item_name, sales, sales_rank
FROM ranked_items
WHERE sales_rank <= 3
ORDER BY category, sales_rank, item_id;

With rank(), tied rows share a rank, and the next rank can be skipped. So if two items tie at rank 3, both are included; if a tie at an earlier rank causes the next rank to be 4, rows at rank 4 are excluded. The result may contain more than three items in a category, or fewer, depending on the sales values. [S1]

Choose based on what “top three” means for your task. Use row_number() when you need no more than three rows per category and can accept a tie-break rule. Use rank() when every item with a qualifying sales rank should remain, including ties at the cutoff. If ties matter but you have not decided how to handle them, confirm that requirement before relying on either result.

Before running the query, check that the table and column names match your database, that the sales column represents the measure you want to rank, and that your chosen tie-breaker is suitable. The examples assume item_id exists and can consistently order equal-sales rows. The SQLite documentation establishes the described window-function behavior; it does not establish your table’s schema, the meaning of its sales values, or which tie policy is right for your use case. [S1]