Selecting Top 200 Rows by Multiple Criteria 1

[pbibler]

I would like to select top rows by multiple criteria. I have division and dollars, and I would like to select the top 200 from each division by dollars. I keep trying things like this:

SELECT TOP 200 DIV, Dollars
FROM [This Week Data]
WHERE ((([This Week Data].DIV)=11))
ORDER BY [This Week Data].[Dollars] DESC
UNION
SELECT TOP 200 DIV, Dollars
FROM [This Week Data]
WHERE ((([This Week Data].DIV)=12))
ORDER BY [This Week Data].[Dollars] DESC

I want the top 200 from divison 11, and the top 200 from division 12. But it doesn't work. The first division in the query comes out right, the next one doesn't give me the top 200, though it gives me 200 rows from that division. Any ideas? It seems like this should be doable, I'm just missing something. The only thing that is working is creating 1 query per division, and individually appending them to a table.

Thanks,

Paul B

 

[PHV]

What about something like this ?
SELECT A.Div, A.Dollars
FROM [This Week Data] AS A INNER JOIN [This Week Data] AS B ON A.Div = B.Div AND A.Dollars <= B.Dollars
WHERE A.Div In (11,12)
HAVING Count(*) <= 200

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pbibler]

Thanks PH, that seems to work. I appreciate the assistance.