Limiting the number of query returns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 28 Nov 2006 13:37 GMT

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

Reply to this Message

Jerry Whittle - 28 Nov 2006 13:52 GMT

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) ;

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> 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
[quoted text clipped - 8 lines]
>
> Dave

Reply to this Message

Dave - 29 Nov 2006 17:51 GMT

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

> 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
[quoted text clipped - 21 lines]
>>
>> Dave

Reply to this Message

Dave - 30 Nov 2006 15:31 GMT

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

>I tried this and the query processed for over an hour before I stopped it.
>The query never completed it's task.
[quoted text clipped - 41 lines]
>>>
>>> Dave

Reply to this Message