John Bergqvist John Bergqvist - 4 months ago 6
SQL Question

SQL - Getting TOP value per attribute in results set

I have a scenario where the following query:

SELECT
Purchasing.Supplier,
PurchasingParts.Part,
PurchasingParts.Description,
PurchasingParts.CostPerUnit,
PurchasingParts.PurchaseOrderNum,
PurchasingParts.ItemNum
FROM PurchasingParts
INNER JOIN Purchasing ON PurchasingParts.PurchaseOrderNum = Purchasing.PurchaseOrderNum
WHERE Supplier = 'Sup1'
ORDER BY PurchasingParts.PurchaseOrderNum DESC


Returns the following results:

Supplier Part Description CostPerUnit PurchaseOrderNum ItemNum
Sup1 cm-14b NULL 9.99 37163 1
Sup1 cm-13 jkljlkjlk 9.99 37160 1
Sup1 cm-13 jkljlkjlk NULL 37158 1


However, I would then like to filter that set of results, so that only the most recent order (i.e. with the Highest PurchaseOrderNum) PER PART (Acting just on the Part attribute) is shown. So in this case, the final results would be:

Supplier Part Description CostPerUnit PurchaseOrderNum ItemNum
Sup1 cm-14b NULL 9.99 37163 1
Sup1 cm-13 jkljlkjlk 9.99 37160 1


I'm using SQL Server 2008.

Answer

You can use ROW_NUMBER() :

SELECT t.* FROM (
    SELECT
        Purchasing.Supplier,
        PurchasingParts.Part,
        PurchasingParts.Description,
        PurchasingParts.CostPerUnit,
        PurchasingParts.PurchaseOrderNum,
        PurchasingParts.ItemNum,
        ROW_NUMBER() OVER(PARTITION BY PurchasingParts.Part ORDER BY PurchasingParts.PurchaseOrderNum DESC) as rnk
    FROM PurchasingParts
    INNER JOIN Purchasing ON PurchasingParts.PurchaseOrderNum = Purchasing.PurchaseOrderNum
    WHERE Supplier = 'Sup1'
    ) t
WHERE t.rnk = 1
ORDER BY t.PurchaseOrderNum DESC