Group By

For sample data in a table called [Analysis]

ID Location Category Partnumber Value -- --------- -------- ---------- ----- 1 here cat1 part001 1 2 there cat1 part002 2 3 wherever cat1 part003 3 4 someplace cat2 part004 4 5 nowhere cat2 part005 5 6 unknown cat2 part006 6

the "ranking query"

SELECT a1.ID, a1.Location, a1.Category, a1.Partnumber, a1.Value, COUNT(*) AS CategoryRank FROM Analysis a1 INNER JOIN Analysis a2 ON a1.Category = a2.Category AND a1.Value <= a2.Value GROUP BY a1.ID, a1.Location, a1.Category, a1.Partnumber, a1.Value

returns

ID Location Category Partnumber Value CategoryRank -- --------- -------- ---------- ----- ------------ 1 here cat1 part001 1 3 2 there cat1 part002 2 2 3 wherever cat1 part003 3 1 4 someplace cat2 part004 4 3 5 nowhere cat2 part005 5 2 6 unknown cat2 part006 6 1

so if you only want the top 2 items in each category just wrap the above query in a SELECT ... WHERE

SELECT *FROM( SELECT a1.ID, a1.Location, a1.Category, a1.Partnumber, a1.Value, COUNT(*) AS CategoryRank FROM Analysis a1 INNER JOIN Analysis a2 ON a1.Category = a2.Category AND a1.Value <= a2.Value GROUP BY a1.ID, a1.Location, a1.Category, a1.Partnumber, a1.Value ) AS RankingQuery WHERE CategoryRank <= 2ORDER BY Category, CategoryRank

to give you

ID Location Category Partnumber Value CategoryRank -- -------- -------- ---------- ----- ------------ 3 wherever cat1 part003 3 1 2 there cat1 part002 2 2 6 unknown cat2 part006 6 1 5 nowhere cat2 part005 5 2

Note: Ensure that the [Category] and [Value] fields are indexed for best performance.