Max Question

[TT]

Hi there,

I think the answer to my question is fairly easy, but for some reason, I
just can't figure it out!! I must still have vacation brain. ;-)

Anyway, here it goes.

I have a table that has three fields:
Vendor
Product
Amount

What I am looking to do is find out the product which has the highest amount
for each vendor.

for example, if the table looked like this:

Vendor Product Amount
1 string 500
1 paper 300
2 pencils 400
2 glue 20

I would want to see a result like this:
1 string 500
2 pencils 400

Is this possible??

If it is, what would happen if I had product for vendor 1 with the same
amount as string in the same table? (eg: Vendor: 1 Product: pens Amount:
500). Would the query give me a duplicate record for Vendor #1 or would it
just pick one of the Products.

Any assistance would be of great help!

Thanks so much in advance!
TT

 

[John Spencer]

SELECT Vendor, Product, Amount
FROM YourTable
WHERE Amount =
(SELECT Max(Amount)
FROM YourTable as Temp
WHERE Temp.Vendor = YourTableVendor)

This will return ties.

If you want to return only one product per vendor, what do you want to use
to determine which product (or do you care)? If you don't care, try

SELECT Vendor, First(Product) as aProduct, Amount
FROM YourTable
WHERE Amount =
(SELECT Max(Amount)
FROM YourTable as Temp
WHERE Temp.Vendor = YourTableVendor)
GROUP BY Vendor, Amount

 

[KARL DEWEY]

I do not follow what you are trying to accomplish. I would think you wanted
to find the vendor with the highest, not the highest thing a vendor has.

Use two queries --
ProductsMax--
SELECT Products.Vendor, Max(Products.Amount) AS MaxOfAmount
FROM Products
GROUP BY Products.Vendor;

SELECT Products.Vendor, Products.Product, Products.Amount
FROM ProductsMax INNER JOIN Products ON (ProductsMax.MaxOfAmount =
Products.Amount) AND (ProductsMax.Vendor = Products.Vendor);

 

[TT]

Thanks John and Karl,

That is what I was looking for.

 

[TT]

Now that I examine my query a little bit, would this give me the same result?

SELECT Company, First(Product) AS FirstProduct, Max(Amount) AS MaxAmount
FROM Table1
GROUP BY Company;

 

[KARL DEWEY]

Why bother to ask? Just try it!

Now that I examine my query a little bit, would this give me the same result?

SELECT Company, First(Product) AS FirstProduct, Max(Amount) AS MaxAmount
FROM Table1
GROUP BY Company;

 

[John Spencer]

No. It will not.

First could give you any product that the company has in this case. It wouldn't
have to be one of the ones taht had the maximum amount.

 

[TT]

Thanks John,

Your answers are MUCH more informative and helpful.

Greatly appriciated!

TT