SQLite: return only top 2 results within each group

This can be achieved by embedding your existing query inside a CTE using WITH, then using it in a WHERE ... IN subquery. The subquery selects LIMIT 2 product IDs from the CTE that match category_name, city, and gender, ordered by product count.

WITH order_groups AS (  SELECT    category_name,    city, gender,    product_id,    COUNT(product_id) AS product_count  FROM orders OO  LEFT JOIN products ON product_id = products.id  LEFT JOIN clients ON client_id = clients.id  GROUP BY product_id, category_name, city, gender  ORDER BY category_name, city, gender, COUNT(product_id) DESC)SELECT * FROM order_groups OG_outerWHERE OG_outer.product_id IN (  SELECT product_id  FROM order_groups OG_inner  WHERE     OG_outer.category_name = OG_inner.category_name AND    OG_outer.city = OG_inner.city AND    OG_outer.gender = OG_inner.gender  ORDER BY OG_inner.product_count DESC LIMIT 2)ORDER BY category_name, city, gender, product_count DESC

This outputs the following rows as requested:

Furniture|London|Female|4|2Furniture|London|Female|3|1Furniture|London|Male|4|3Furniture|London|Male|3|2Furniture|NY|Female|5|2Furniture|NY|Female|4|1Furniture|NY|Male|3|3Furniture|NY|Male|4|1Kitchen|London|Female|9|2Kitchen|London|Female|8|1Kitchen|London|Male|9|3Kitchen|London|Male|8|1Kitchen|NY|Female|9|4Kitchen|NY|Female|10|2Kitchen|NY|Male|1|1Kitchen|NY|Male|8|1Sport|London|Female|7|2Sport|London|Female|2|1Sport|London|Male|7|2Sport|London|Male|6|1Sport|NY|Female|2|2Sport|NY|Female|6|2Sport|NY|Male|7|3