There are 3 tables: Products (id / name / price), Categories (id / name) and and a table implementing many-to-many relationships (id / idProduct / idCategories)

Please tell me how to write the correct sql query that would output: the name of the category, the name of the product which has the maximum price in this category, the price of this product.

Find the maximum price for the category and then connect for the price with the goods not to offer)) I am looking for a more correct and beautiful solution

  • 2
    What DBMS do you use? - Nick Proskuryakov
  • 2
    Numbering CTE by category, sorted by price, return of the first records. - Akina
  • I use ms sql, but the query (in theory) should not depend on the subd and be built on pure sql - Ruslan

1 answer 1

Thanks for the help with CTE, solved this problem like this:

With CTE As (select Row_Number() Over(Partition by KategoriyaId Order By price desc) As Row_Num, t.id, t.price, t.name, s.KategoriyaId from tovar as t left join Svyaz as s on s.TovarId = t.Id) Select KategoriyaId, price, name From CTE Where ROW_NUM = 1

Update. Option without CTE

 select KategoriyaId, name, price from ( select Row_Number() Over(Partition by KategoriyaId Order By price desc) As Row_Num, t.id, t.price, t.name, s.KategoriyaId from tovar as t left join Svyaz as s on s.TovarId = t.Id where KategoriyaId is not null) as t1 where Row_Num = 1