Return 10 most expensive & 10 cheapest products in each category

Question

I have a table (products) with 3 columns (product_id, category_id, price), there are more than 3000 categories, I want to return 10 most expensive products and 10 cheapest products for each of them (sorted by price DESC), in ONE QUERY...

already wrote this code:
SELECT * FROM products ORDER BY category_id, price DESC
LIMIT 10

but this only limits 10 row for total result, don't know how to limit each category to 10.

I don't know how to use rank() or partition by, someone help me please.

Answer 1

SQL UNION Operator

https://www.w3schools.com/sql/sql_union.asp

Keep learning & happy coding :D

Answer 2

thanks but that wasn't what I wanted to do...

Answer 3

Can you clarify what is the shape of the result that you try to get from the query? From your description it seems like you want 3000 rows (one for each category) and 20 columns (top10 and bottom10 values)?

Answer 4

Also you need to be more clear which SQL dialect you are using, because there may be some variations between syntax.

https://docs.microsoft.com/en-us/sql/t-sql/functions/rank-transact-sql?view=sql-server-ver15

https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-rank/