0

Return 10 most expensive & 10 cheapest products in each category

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.

17th Sep 2021, 12:37 PM
Ali Abdi
Ali Abdi - avatar
4 ответов
0
SQL UNION Operator https://www.w3schools.com/sql/sql_union.asp Keep learning & happy coding :D
17th Sep 2021, 1:47 PM
SoloProg
SoloProg - avatar
0
thanks but that wasn't what I wanted to do...
17th Sep 2021, 2:57 PM
Ali Abdi
Ali Abdi - avatar
0
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)?
18th Sep 2021, 9:35 AM
Tibor Santa
Tibor Santa - avatar
0
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/
18th Sep 2021, 9:46 AM
Tibor Santa
Tibor Santa - avatar