CODEWITHSUNDEEP
sqlsql-server
John Bergqvist
John Bergqvist

Reputation: 1003

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.

Upvotes: 1

Views: 39

Answers (1)

sagi
sagi

Reputation: 40491

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

Upvotes: 2

Related Questions