Get the top 3 items of a join

Question

I have 3 tables: product, category and product_category (which products are in each category).

I'd like to get the top 3 most expensive product in each category.

I have this basic relation:

select c.name
     , p.id
     , p.price 
  from category c
  left 
  join product_category pc
    on pc.category_id = category.id
  left 
  join product p 
    on pc.product_id = p.id

But now I would like to get just the 3 most expensive of each category.

In this case we can use any number of joined tables and it can be escalated to any more complex query.

Is this possible without a loop ?

I'm using 10.2.14-MariaDB-log with this schema http://sqlfiddle.com/#!9/43035a

Can you provide your database schema in a [sqlfiddle](http://sqlfiddle.com/) — UkFLSUI, Jun 23 '18 at 11:15
See [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Jun 23 '18 at 11:33
Possible duplicate of [Get top n records for each group of grouped results](https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — UkFLSUI, Jun 23 '18 at 13:15
[_Efficient groupwise max_](http://mysql.rjweb.org/doc.php/groupwise_max). — Rick James, Jun 25 '18 at 04:54

Kamil Gosciminski · Accepted Answer · 2018-06-24T07:19:25.113

MySQL 8.0+ & MariaDB 10.2+ support window functions like dense_rank which are suited to handle your case. For each category we assign ranks based on product price and pull only those, who are in the top 3. Use of dense_rank properly handles ties which means that if there are products for a category with the same price there can be more than 3 rows in the output for a particular category. If this behaviour is not the preferred one and you would rather want to see max 3 rows in the output, discarding ties, use row_number window function instead.

select name, id
from (
  select c.name, p.id, dense_rank() over (partition by c.id order by p.price desc) as rank
  from category c
  left join product_category pc on pc.category_id = c.id
  left join product p on pc.product_id = p.id
) t
where rank <= 3

Get the top 3 items of a join

1 Answers1