sql server - SQL - Getting TOP value per attribute in results set -

i have scenario following query:

select purchasing.supplier, purchasingparts.part, purchasingparts.description, purchasingparts.costperunit, purchasingparts.purchaseordernum, purchasingparts.itemnum purchasingparts inner join purchasing on purchasingparts.purchaseordernum = purchasing.purchaseordernum supplier = 'sup1' order purchasingparts.purchaseordernum desc 

returns 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, filter set of results, recent order (i.e. highest purchaseordernum) per part (acting on part attribute) shown. in case, final results 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.

you can use row_number() :

select t.* (     select         purchasing.supplier,         purchasingparts.part,         purchasingparts.description,         purchasingparts.costperunit,         purchasingparts.purchaseordernum,         purchasingparts.itemnum,         row_number() over(partition purchasingparts.part order purchasingparts.purchaseordernum desc) rnk     purchasingparts     inner join purchasing on purchasingparts.purchaseordernum = purchasing.purchaseordernum     supplier = 'sup1'     ) t t.rnk = 1 order t.purchaseordernum desc