Max Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TT - 26 May 2006 15:48 GMT

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

Reply to this Message

John Spencer - 26 May 2006 16:48 GMT

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

> Hi there,
>
[quoted text clipped - 37 lines]
> Thanks so much in advance!
> TT

Reply to this Message

TT - 26 May 2006 17:37 GMT

Thanks John and Karl,

That is what I was looking for.

Signature

> SELECT Vendor, Product, Amount
> FROM YourTable
[quoted text clipped - 57 lines]
> > Thanks so much in advance!
> > TT

Reply to this Message

TT - 26 May 2006 17:58 GMT

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;

Signature

> SELECT Vendor, Product, Amount
> FROM YourTable
[quoted text clipped - 57 lines]
> > Thanks so much in advance!
> > TT

Reply to this Message

KARL DEWEY - 26 May 2006 19:31 GMT

Why bother to ask? Just try it!

> Now that I examine my query a little bit, would this give me the same result?
>
[quoted text clipped - 63 lines]
> > > Thanks so much in advance!
> > > TT

Reply to this Message

John Spencer - 27 May 2006 01:12 GMT

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.

> Now that I examine my query a little bit, would this give me the same result?
>
[quoted text clipped - 66 lines]
> > > Thanks so much in advance!
> > > TT

Reply to this Message

TT - 29 May 2006 17:34 GMT

Thanks John,

Your answers are MUCH more informative and helpful.

Greatly appriciated!

TT

> No. It will not.
>
[quoted text clipped - 71 lines]
> > > > Thanks so much in advance!
> > > > TT

Reply to this Message

KARL DEWEY - 26 May 2006 16:49 GMT

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);

> Hi there,
>
[quoted text clipped - 34 lines]
> Thanks so much in advance!
> TT

Reply to this Message