Top 3 Query Problem

[paulostrom]

A while back (thread232-1437399)I needed help with a query that would return the highest 3 daily sales amounts from multiple locations.

r937 pointed me in the right direction with this query:

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

It has been working like a gem until I noticed that if the third and fourth highest daily sales amounts are equal, the query returns the four highest amounts instead of three.

Does anyone have any suggestions on how to adjust for this anomoly?

I'm using an Access DB.

 

[r937]

yes, i have an idea on how to "adjust" for this anomaly...

don't do anything!!!

otherwise, you are misrepresenting the actual data



in fact, ms access even has special syntax for this scenario, TOP n WITH TIES (although it's clunky if you try to apply it to multiple groups, as in top 3 per day)

r937.com | rudy.ca

 

[paulostrom]

I understand the logic and I guess a better question might be how can I create an anomaly that doesn't include ties?

I'm taking the results from this query and grouping by location for a sum total of the Top 3 days from each location. When that 4th day gets in there my desired results are skewed.

 

[r937]

how do you want to resolve the tie? you can do this only if you can specify a column, the values of which can be used to break the tie

top 3 students in each class -- if tie for third place, pick the kid with blond hair and blue eyes

top 3 runners in each sprint race -- if tie for third place, pick the runner with nike shoes

top 3 days for each location -- if tie for third place, pick the later day

r937.com | rudy.ca

 

[paulostrom]

The later date would work since there is a date column in the same table. I played with that concept this morning for a while but was not successful. How can that query be modified to make a date evaluation?

 

[r937]

add the date as the 3rd ORDER BY column, then, as you loop over the returned results with coldfusion, just display the top 3, ignoring the 4th and 5th and any other ties

r937.com | rudy.ca

 

[paulostrom]

Thanks for the help r937. Your advice was perfect.