Topic: How to make request

There are categories and products
id, category, buys
1, name1, 400
2, name2, 200
3, name3, 700
id, cat_id, product, buys
1, 1, name1, 100
2, 1, name2, 250
3, 2, name3, 80
4, 3, name4, 300
How to make request to select categories with sorting on buys desc and to select on one goods from this category with max (buys)


Re: How to make request

FAQ: Sampling of first/last record in groups


Re: How to make request

Deduces all goods in categories, it is necessary on one with max (buys)

select p1.*
from categories s left join products p1
on s.id = p1.cat_id
left join products p2
on p1.cat_id=p2.cat_id and p1.buys <p2.buys
order by s.buys desc, p1.buys desc


Re: How to make request

The link is given. Read and use.