Clipper87 Clipper87 - 2 months ago 14
SQL Question

left join without duplicate values using MIN()

I have a table_1:

id custno
1 1
2 2
3 3


and a table_2:

id custno qty descr
1 1 10 a
2 1 7 b
3 2 4 c
4 3 7 d
5 1 5 e
6 1 5 f


When I run this query to show the minimum order quantities from every customer:

SELECT DISTINCT table_1.custno,table_2.qty,table_2.descr
FROM table_1
LEFT OUTER JOIN table_2
ON table_1.custno = table_2.custno AND qty = (SELECT MIN(qty) FROM table_2
WHERE table_2.custno = table_1.custno )


Then I get this result:

custno qty descr
1 5 e
1 5 f
2 4 c
3 7 d


Customer 1 appears twice each time with the same minimum qty (& a different description) but I only want to see customer 1 appear once. I don't care if that is the record with 'e' as a description or 'f' as a description.

Answer

"Generic" SQL way:

SELECT table_1.custno,table_2.qty,table_2.descr 
FROM table_1, table_2 
WHERE table_2.id = (SELECT TOP 1 id 
                    FROM table_2 
                    WHERE custno = table_1.custno 
                    ORDER BY qty )

SQL 2008 way (probably faster):

SELECT custno, qty, descr 
FROM
(SELECT 
    custno, 
    qty, 
    descr,
    ROW_NUMBER() OVER (PARTITION BY custno ORDER BY qty) RowNum
    FROM table_2
) A
WHERE RowNum = 1
Comments