I really need help with this one.

I really need help with this, I am sure it cant be too hard, I have
just been looking at it too long.
I have part numbers and vendor numbers and I would like a query to show
a distinct part number and vendor derived from the vendor that has
delivered the most of that part.
The reason I have more than one vendor for each part, is that incoming
from a warranty repair from the same vendor can appear as seperate from
the mass production delivery. for instance
This is an example of my data:
PART Vendor QTY Delivered
-- -- --
12345-1 125a 20
12345-1 5456c 35
12345-1 25a 5
12345-1 72v 10000 <-Max delivered for Part/Vend
12345-1 89c 12
99998-7 51 100000 <-Max delivered for part/Vend
99998-7 52 8
99998-7 53 5
I would then like the data to appear as below:
PART Vendor QTY Delivered
-- -- --
12345-1 72v 10000
99998-7 51 100000
It would then only display the part and vendor where the Qty is
highest. I also only ever see one instance of any part number, though a
vendor number can appear each time the vendor delivers the most of that
part.
Thanks in advance for any help you can give me.
PhilSELECT *
FROM Deliveries as D
WHERE QtyDelivered =
(select max(QtyDelivered) from Deliveries as M
where D.Part = M.Part)
One point this does not address is when two vendors both have the same
QtyDelivered for a part. As written, it will show two rows for the
part.
Roy Harvey
Beacon Falls, CT
On 2 May 2006 10:47:26 -0700, "philipbennett25" <pbennett@.xyratex.com>
wrote:

>I really need help with this, I am sure it cant be too hard, I have
>just been looking at it too long.
>I have part numbers and vendor numbers and I would like a query to show
>a distinct part number and vendor derived from the vendor that has
>delivered the most of that part.
>The reason I have more than one vendor for each part, is that incoming
>from a warranty repair from the same vendor can appear as seperate from
>the mass production delivery. for instance
>
>This is an example of my data:
> PART Vendor QTY Delivered
>-- -- --
>12345-1 125a 20
>12345-1 5456c 35
>12345-1 25a 5
>12345-1 72v 10000 <-Max delivered for Part/Vend
>12345-1 89c 12
>99998-7 51 100000 <-Max delivered for part/Vend
>99998-7 52 8
>99998-7 53 5
>
>I would then like the data to appear as below:
> PART Vendor QTY Delivered
>-- -- --
>12345-1 72v 10000
>99998-7 51 100000
>
>It would then only display the part and vendor where the Qty is
>highest. I also only ever see one instance of any part number, though a
>vendor number can appear each time the vendor delivers the most of that
>part.
>Thanks in advance for any help you can give me.
>Phil