top X results per day with sql in access

Question

I am trying to get the top 2 results per day, where the "top" results are the rows with the highest "MPR". My table looks like this:

date      symbol   MPR
8/7/2008  AA       0.98
8/7/2008  AB       0.97
8/7/2008  AC       0.96
...
8/7/2008  AZ       0.50
8/8/2008  AA       0.88
8/8/2008  AB       0.87
8/8/2008  AC       0.86
...
8/8/2008  AZ       0.40
...
many other days

I would like the result set to be:

date      symbol   MPR
8/7/2008  AA       0.98
8/7/2008  AB       0.97
8/8/2008  AA       0.88
8/8/2008  AB       0.87

I have tried using the TOP keyword, but this only gives me the top 2 rows, and I'm trying to do the top 2 rows per date.

I am using Microsoft Access. I'd appreciate any help! :-)

Answer 1

http://www.rogersaccesslibrary.com/forum/forum%5Fposts.asp?TID=233

Check page 5

Here is the solution:

SELECT t1.date, t1.symbol, t1.MPR
FROM table1 t1
WHERE t1.MPR IN
(
  SELECT TOP 2 t2.MPR FROM table1 t2
  WHERE
  t2.date = t1.date
  ORDER BY t2.MPR DESC
)

Answer 2

Something like..

     SELECT DISTINCT(symbol), date, MAX(MPR) 
     FROM [TABLE]
     GROUP BY date,symbol

should do it... or symbol,date switched in the GROUP BY.

Answer 3

Here you go:

    SELECT YourTable.DateStamp, YourTable.Symbol, Max(YourTable.MPR) AS MaxOfMBR
    FROM YourTable
    GROUP BY YourTable.DateStamp, YourTable.Symbol, YourTable.MPR
    HAVING YourTable.MPR In (SELECT TOP 2 MPR FROM YourTable T2 WHERE 
    YourTable.DateStamp = T2.DateStamp ORDER BY MPR DESC)
    ORDER BY YourTable.DateStamp, YourTable.MPR DESC;

The trick here is to use an aggregate query, and then in the HAVING statement (basically the "WHERE" of an aggregate query), you use a subquery to pull the top 2 values from the MPR column.

I made a quick DB in Access 2007 and this worked fine.

EDIT: Clarification on the usage of MAX here. You're returning the MAX of a group, not the MAX value of the column for a particular day. Therefore, you get the correct number of records (2) per date.