Limiting the number of query returns

[Dave]

I want to run a query that is grouped by city and product dollars in each
city. I want to limit the return to the top 5 products by dollars for each
city.

I can run a query and roll up the dollars but I don not know how to limit
the number of returns.

Can anyone get me started?

Thanks

Dave

 

[Jerry Whittle]

Something like below with the proper field and table names will work. The
Top5 below is the name of the table. Notice that it is used twice with
different aliases (T1 and T2).

SELECT T1.Field1, T1.Field2
FROM Top5 AS T1
WHERE T1.Field2 In
(SELECT TOP 5 T2.Field2
FROM Top5 AS T2
WHERE T2.Field1 = T1.Field1
ORDER BY T2.Field2 DESC) ;

 

[Dave]

I tried this and the query processed for over an hour before I stopped it.
The query never completed it's task.

SELECT T1.Field1, T1.Field2
FROM Top5 AS T1
WHERE T1.Field2 In
(SELECT TOP 5 T2.Field2 <===I changed TOP 5 T2.Field2 to
just T2.Field2=====>
FROM Top5 AS T2
WHERE T2.Field1 = T1.Field1
ORDER BY T2.Field2 DESC)

How does it stop at just 5 items per group?

Thanks

Dave

 

[Dave]

I did need to use the Top 5 - it works after I reduced the data. Thanks