> > Dear All, > > I have the following problem with grouping: I want to know the maximum > in a group as well as the maximal element. Example: > > I have a table products_providers: > product | provider | criteria_1 | criteria_2 > > I have a number of products, each of them from a several providers. > Each product is described by two numeric values. I can easily select > the best value for each product by a given criteria, like: > > select product, max(criteria_1) from products_providers group by > product; > > but I need to know the best-scoring provider as well. > > Result I need should look like: > product | best_provider_1 | best_criteria_1 | best_provider_2 | > best_criteria_2 > > If it counts results may be split into two tables: one for the first > and the other for the second criteria > > Can you help me with a painless solution? Is something like this what you're after? select * from products_proivders order by criteria_1 desc limit 1 You can get the best providers for both criteria using union like this: select * from ( select 'best_criteria_1' as name, product, provider, criteria_1, criteria_2 from products_proivders order by criteria_1 desc limit 1 ) x union select * from ( select 'best_criteria_2' as name, product, provider, criteria_1, criteria_2 from products_proivders order by criteria_2 desc limit 1 ) y
