Problem selecting TOP values by category and date

[djar]

Hello,

I need to add ranking to a table of values, and even through I've gone
through everything in the group, I can't pinpoint my problem.

Here goes... I have the following resulting query

Date MeasureID Store Perf
----------------------------------------------------------
1/1/2007 1 5 100.00%
1/1/2007 1 1 100.00%
1/1/2007 1 4 97.00%
1/1/2007 1 2 89.00%
1/1/2007 1 3 89.00%
1/1/2007 2 2 100.00%
1/1/2007 2 3 100.00%
1/1/2007 2 1 100.00%
1/1/2007 2 4 100.00%
1/1/2007 2 5 75.00%
1/2/2007 ...

This is the crude data I receive daily and is updated in an access
table.

From this data I need to Extract the Top 2 Stores for every measure

for every date, resulting in a query like this:

Date MeasureID Store Perf
----------------------------------------------------------
1/1/2007 1 5 100.00%
1/1/2007 1 1 100.00%
1/1/2007 2 2 100.00%
1/1/2007 2 3 100.00%
1/2/2007 ...

If I could add a column with numbering to the oridinal data, I could
jsut query out number = 1 or 2. But as I have duplicate values in the
performance and I have mo unique ID for each record, I cannot find a
way.


Any help you be greatly appreciated.

Regards,

Diego

 

[John Spencer]

What do you want in case of ties?
Let's say on 1/1/2007 all 5 stores scored 100 on Measure 1. WHO gets
dropped?

The following should work for you. It could be slow depending on the number
of records involved

SELECT *
FROM Table as T1
WHERE Format(MeasureID,"0000") & Store
In (SELECT Top 2 Format(MeasureID,"0000") & Store
FROM Table as T2
WHERE T1.MeasureID = T2.MeasureID
AND T1.[Date] = T2.[Date]
ORDER BY PERF Desc, StoreID)

If you are limiting records to a specific date range then you might be able
to make this faster by adding the date range criteria to both the main query
and to the subquery - especially if the Date field (bad name for a field,
Date is a reserved word in Access) is indexed.

SELECT *
FROM Table as T1
WHERE Format(MeasureID,"0000") & Store
In (SELECT Top 2 Format(MeasureID,"0000") & Store
FROM Table as T2
WHERE T1.MeasureID = T2.MeasureID
AND T1.[Date] = T2.[Date]
AND T2.[Date] Between #1/1/2007# and #1/31/2007#
ORDER BY PERF Desc, StoreID)
AND T1.[Date] Between #1/1/2007# and #1/31/2007#
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Try something like

Select T1.Date , T1.MeasureID, T1.Store, T1.Perf
From TableName As T1
Where T1.Store In (Select Top 2 T2.Store
From TableName As T2
Where T2.MeasureID = T1.MeasureID
Order By T2.Perf Desc)

Note: there is no fixed order about the stores are picked up, incase there
are more then two stores that are in the same Perf.