Re: Simple ranking within group

On Fri, 14 Oct 2005 11:50:08 -0700, AshleyT wrote:

>I have a table that has:
>
>sku, vendor_number, vendor_item_number.
>
>Right now there are multiple vendor_item_numbers to 1 sku. I need to find
>just 1 vendor_item_number per sku (doesn't matter which one.) So if I knew
>how to find the TOP 1 in each group (sku, vendor_number) that would do the
>trick.
>
>So if anyone knows how to do that, it would be greatly appreciated.

Hi Ashley,

Unfortunately, SQL Server has no "GimmeOneDontCareWhich" function. If
you want one from the group, you'll have to specify which one.

Assuming lowest vendor number is okay, here's how to get the sku and the
vendor number:

SELECT sku, MIN(vendor_number)
FROM YourTable
GROUP BY sku

If you need to include vendor_item_number in the output as well, you'll
have to make it slightly more complicated. There are two ways to do
this. Version 1:

SELECT a.sku, a.vendor_number, a.vendor_item_number
FROM YourTable AS a
WHERE NOT EXISTS (SELECT *
FROM YourTable AS b
WHERE b.sku = a.sku
AND b.vondor_number < a.vendor_number)

Version 2:

SELECT a.sku, a.vendor_number, a.vendor_item_number
FROM YourTable AS a
INNER JOIN (SELECT sku, MIN(vendor_number)
FROM YourTable
GROUP BY sku) AS b (sku, MinVendorNo)
ON a.sku = b.sku
AND a.vendor_number = b.MinVendorNo


Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.