Imagine data as below:-

category_id	item_name
1	Item A1
1	Item A2
1	Item A2
2	Item B1
2	Item B1
2	Item B2
3	Item C1
3	Item C2

SELECT 
    category_id, 
    item_name,
    ROW_NUMBER() OVER(PARTITION BY category_id, item_name ORDER BY category_id, item_name) AS displayOrder
FROM 
    items
ORDER BY 
    category_id, displayOrder;

In this query:

• ROW_NUMBER() OVER(PARTITION BY category_id, item_name ORDER BY category_id, item_name): The PARTITION BY clause partitions the result set by both category_id and item_name. ROW_NUMBER() then assigns a sequential number (displayOrder) starting from 1 within each partition, ordered first by category_id and then by item_name.
• SELECT category_id, item_name: Selects only category_id and item_name from the items table.
• ORDER BY category_id, displayOrder: Orders the result set first by category_id and then by displayOrder, ensuring that items within each category_id and item_name group are ordered sequentially based on category_id and item_name.

Now, let’s see the result of executing this modified query on our sample data:

category_id	item_name	displayOrder
1	Item A1	1
1	Item A2	2
1	Item A2	3
2	Item B1	1
2	Item B1	2
2	Item B2	3
3	Item C1	1
3	Item C2	2

In this result:

• The item_id is not included.
• displayOrder starts from 1 within each combination of category_id and item_name, ordered first by category_id and then by item_name.
• Items are ordered first by category_id, and within each category_id, items with the same item_name are ordered by item_name.

This query structure maintains the sequential ordering (displayOrder) within each unique combination of category_id and item_name, ensuring the rows are ordered by both criteria within their respective partitions.