SQL How to select first match if key has multiple values

Question

Folks,

I have the following table...

Code UPC
1111 A
1111 B
1111 C
2222 B
2222 A

I need to return a table where only one UPC is returned per code (any code), so for table below result should only be 2 rows such as...

Code UPC
1111 A
2222 B

Appreciate any pointers.

What is your RDBMS? SQL Server? MySQL? Oracle? Something else? — Siyual, Jan 13 '16 at 21:49
Be careful with the idea of "first match". How do you define "first"? **Rows don't have an inherent order.** In your example, you have "2222 B" as the "first" match, but it's only "first" in the sense of that's the order you have shown it in the listing. Both the example from Siyual and Joe Stefanelli below are picking the first as defined by "Order by UPC", which is NOT the same as what you have. If you're wanting "first" in the sense of "in which order the rows were added" then you'll have to have a timestamp and order on that. — Andy Lester, Jan 13 '16 at 21:56

score 1 · Answer 1 · answered Jan 13 '16 at 21:51

For SQL Server, you can use a ROW_NUMBER() windowed function in a CTE for this:

;With Cte As 
(
    Select  Code, UPC,
            Row_Number() Over (Partition By Code Order By UPC Asc) RN
    From    YourTable
)
Select  Code, UPC
From    Cte
Where   RN = 1

score 1 · Accepted Answer · answered Jan 13 '16 at 21:52

You can leverage a Common Table Expression (CTE) and the row_number function to number the UPC values within each group of Codes, adjusting the ORDER BY to suit your ultimate needs.

WITH cteCodeUPC AS (
    SELECT Code, UPC,
           ROW_NUMBER() OVER(PARTITION BY Code ORDER BY UPC) AS RowNum
        FROM YourTable
)
SELECT Code, UPC
    FROM cteCodeUPC
    WHERE RowNum = 1;

SQL How to select first match if key has multiple values

2 Answers2