top three within a group

[Jordie]

Hello all,

I am attempting to run a query that shows the three highest values by group. This is for an audit program that has four layers (ecoat, primer, e1 and e2). I want the top three values for each of these to be displayed. I can do the top three (select top 3), but this doesn't do the top 3 for each group. Any ideas?

 

[CT Witter]

See

ACC2000: How to Create a "Top N Values Per Group" Query
http://support.microsoft.com/kb/210039

HTH,
CT

 

[Jordie]

I've already tried that link. Method 1, which looked easier, was setup for two tables :

(Criteria: In (Select Top 3 [UnitsInStock] From Products Where [CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock] Desc) ).

All of my data is collected from one table, and I don't seem to be able to make it work. In fact, I've even tried a simple test database with letters in one column, and number in the other. I wanted the top three numbers for each letter. Is this code useful for that?

 

[giacomo]

try this...

SELECT  
CategoryID
, UnitsInStock
FROM 
Products as x
WHERE
UnitsInStock in 
( SELECT  top 3
  UnitsInStock
  FROM Products
  WHERE CategoryID = x.CategoryID
  ORDER BY UnitsInStock desc
);

hth,
Giacomo