How to get the name of the category, the name of the product with the maximum price and the price?

Question

SQL

Actually, a subject...
There is a table of categories Categories. There is a table of products (Products) with a link to a category via category_id.
You need to get a list of products with a maximum price in each category, and the price of this product.
I got the following request, but I can not get the name of the product:

select
  cat.name as 'категория',
  MAX(product.price) as 'максимальная цена'
from Categories cat
inner join Products product on cat.id = product.category_id
group by cat.name

Can you tell me how to get the name of the product with the maximum price?

Thank you.

Answer 1

If the sql dialect supports ranking functions (see window functions) - you can dance from them. Then the query will also be readable and beautiful.
Otherwise, you will have to dance.

Answer 2

What, there's no easy way? Maybe built-in select?

Answer 3

Try to add a comma-separated product name in group by. In general, if one product is needed, I would make a selection from the product table and add categories to it.

Answer 4

with cat_max(cat_id, max_price) as 
(
  select category, MAX(price)
  from Product
  group by category
)

select c.name as 'Category', p.name as 'Product', cm.max_price as 'Max price'
from Product as p join cat_max as cm
  on p.price = cm.max_price
  and p.category = cm.cat_id
  join Category as c on c.id = cm.cat_id