Limiting Query Results

[paulostrom]

Suppose I have a table with the fields "location", daily_sales", and "date". There are 3 different locations and I want the Top 3 "daily_sales" for each location.

I know this can easily be done using separate queries for each location (i.e., Select Top 3 Daily_Sales from Sales where location = "A" Order By Daily_Sales DESC). Unfortunately, there are actually thousands of "locations" in my database and using separate queries doesn't seem to be very scalable.

I've made various attempts using grouping, query of queries, and nested queries but can't seem to get the results I need. This seems like a simple enough problem and I'm hoping there is a simple solution that I am just overlooking. Any suggestions would be greatly appreciated.

 

[r937]

since there are so many variations of the structured query language, it is usually helpful if you mentioned which database system you're querying

sybase? access? db2? mysql? informix? foxpro? postgresql?

however, in this particular case, i believe the following should work for you:

select location
     , Daily_Sales
  from Sales as T
 where ( select count(*) 
           from Sales  
          where location
            = T.location
            and Daily_Sales 
            > T.Daily_Sales ) < 3
order
    by location
     , Daily_Sales desc

r937.com | rudy.ca

 

[paulostrom]

It's an Access DB.

Your solution works perfectly. Thanks r937!

 

[paulostrom]

How about if "Sales" was a query instead of a table? Is it possible to do the same thing for a QoQ?

 

[r937]

you could certainly try it, although QoQ has certain limitations, and while i can't recall off the top of my head, a subquery might not be allowed

my preferred strategy would be to apply the logic in the database query itself, rather than QoQ

with microsoft access you're in good shape, because access queries can be treated exactly like tables

r937.com | rudy.ca