I have a list of product sales for various regions.
How do I write a query to only retrieve the record with the highest value in a region, but include the region and product code in the results?
For example, If I run an aggregate query using the max function I still get the record for region:
SELECT Stats.ProdCode, Max(Stats.Sales) AS MaxOfSales, Stats.Region
FROM Stats
GROUP BY Stats.ProdCode, Stats.Region;
I realise that this returns the record for each region because the 'Group By' is applied to that field, but if I remove it then that field is not going to be available in the results(???)
what I'm looking for is a result like (in table format of course);
ProdCode: AX23
MaxOfSales: 1,5022
Region: WestA2
How do I write a query to only retrieve the record with the highest value in a region, but include the region and product code in the results?
For example, If I run an aggregate query using the max function I still get the record for region:
SELECT Stats.ProdCode, Max(Stats.Sales) AS MaxOfSales, Stats.Region
FROM Stats
GROUP BY Stats.ProdCode, Stats.Region;
I realise that this returns the record for each region because the 'Group By' is applied to that field, but if I remove it then that field is not going to be available in the results(???)
what I'm looking for is a result like (in table format of course);
ProdCode: AX23
MaxOfSales: 1,5022
Region: WestA2
need help using the max function in aggregate query
0 commentaires:
Enregistrer un commentaire