How do I assign a row number within each category?

Use SQLite’s ROW_NUMBER() window function with the category as the partition:

ROW_NUMBER() OVER (
  PARTITION BY category
  ORDER BY price, product_id
) AS category_row

PARTITION BY category restarts the numbering for each category. Within each partition, ROW_NUMBER() assigns consecutive numbers starting at 1 according to the window’s ORDER BY. [S1]

Here, price puts cheaper products first, and product_id breaks ties so products with the same price have a predictable order. This assumes product_id is unique in your data. The window ordering determines the row numbers, but not the final display order; use an outer ORDER BY to arrange the completed results. [S1]

Here is a self-contained example with invented product names and prices. The values are only for illustration:

WITH products(product_id, category, product, price) AS (
  VALUES
    (1, 'Books', 'Atlas', 12.00),
    (2, 'Books', 'Notebook', 8.00),
    (3, 'Books', 'Dictionary', 12.00),
    (4, 'Kitchen', 'Mug', 8.00),
    (5, 'Kitchen', 'Bowl', 8.00),
    (6, 'Kitchen', 'Spoon', 3.00)
)
SELECT
  category,
  product,
  price,
  ROW_NUMBER() OVER (
    PARTITION BY category
    ORDER BY price, product_id
  ) AS category_row
FROM products
ORDER BY category, category_row;

The result is:

Books   | Notebook   |  8.00 | 1
Books   | Atlas      | 12.00 | 2
Books   | Dictionary | 12.00 | 3
Kitchen | Spoon      |  3.00 | 1
Kitchen | Mug        |  8.00 | 2
Kitchen | Bowl       |  8.00 | 3

The numbering starts over for Kitchen. Atlas and Dictionary have the same price but receive different row numbers: ROW_NUMBER() gives each row its own consecutive position. The product_id tie-breaker places Atlas before Dictionary in this example. [S1]

Change the window ordering to ORDER BY price DESC, product_id:

ROW_NUMBER() OVER (
  PARTITION BY category
  ORDER BY price DESC, product_id
) AS category_row

Now the higher prices come first within each category. The product_id still breaks ties; it is ascending here, but you can choose its direction to suit your intended ordering. Add an outer ORDER BY, such as ORDER BY category, category_row, if you want the displayed results to follow the category and row-number order. The window’s ORDER BY controls numbering, not the final output order. [S1]

Check these parts separately:

• Confirm PARTITION BY names the category column you intend. That is what makes numbering restart by category. [S1]
• Check the window’s ORDER BY: use price for cheapest first or price DESC for most expensive first.
• If equal prices need a predictable order, include a tie-breaker that is unique for the rows being numbered. [S1]
• If the numbers are right but the rows appear in an unexpected sequence, add or adjust the outer ORDER BY. Window ordering alone does not set the final display order. [S1]

For the example above, ORDER BY category, category_row displays each category together and its products in row-number order.