obtain TOP n values per day (multiple days)

[Guest]

Help. I need to return the top records (based on highest Count) by
day....any suggestions? This is what I have so far:

SELECT Date1, Field2, Count([ID])
FROM Table1
GROUP BY Date1, Field2
ORDER BY Date1, Count([ID]) DESC

 

[Michel Walsh]

Hi,

Make a query that does the count


SELECT Field2, Date1, COUNT( ID ) As myCount
FROM myTable
GROUP BY Field2, Date1


Save it as, say, q1.


Next, make the following:


SELECT Date1, MAX(myCOUNT) As myMax
FROM q1
GROUP BY Date1

Save it as, say q2.

Finally,


SELECT q1.*
FROM q1 INNER JOIN q2
ON q1.Date1= q2.Date1 AND q1.MyCount=q2.MaxCount




The first query computes the counts (per Date1, per Field2). The second
query find the max of the counts, per day, the third query picks up the
whole records in q1 that "matches" the max found, per date, in the second
query.




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hi Michel.
Your response was brilliant! I like your logic and although you did answer
the question, I didn't phrase my question correctly. I actually need to
return top 30 records for each day.....sorry!!!

Hi,

Make a query that does the count


SELECT Field2, Date1, COUNT( ID ) As myCount
FROM myTable
GROUP BY Field2, Date1


Save it as, say, q1.


Next, make the following:


SELECT Date1, MAX(myCOUNT) As myMax
FROM q1
GROUP BY Date1

Save it as, say q2.

Finally,


SELECT q1.*
FROM q1 INNER JOIN q2
ON q1.Date1= q2.Date1 AND q1.MyCount=q2.MaxCount




The first query computes the counts (per Date1, per Field2). The second
query find the max of the counts, per day, the third query picks up the
whole records in q1 that "matches" the max found, per date, in the second
query.




Hoping it may help,
Vanderghast, Access MVP

Help. I need to return the top records (based on highest Count) by
day....any suggestions? This is what I have so far:

SELECT Date1, Field2, Count([ID])
FROM Table1
GROUP BY Date1, Field2
ORDER BY Date1, Count([ID]) DESC

 

[Michel Walsh]

Hi,


In such case, we need to combine the second and third query into one:


SELECT Field2, Date1, myCount
FROM q1 As a
WHERE myCount IN( SELECT TOP 30 myCount
FROM q1 As b
WHERE a.Date1=b.Date1
ORDER BY b.myCount DESC)
ORDER BY Date1 ASC, myCount DESC



The inner most query select the top 30 records, for a "given" date (the date
of "a.Date1"), but keeping only the myCount value of each record.

The outermost query keep the record if it is in the top 30 returned by the
innermost query, for the actual "Date1" of the record we are in evaluation
that is.



Hoping it may help,
Vanderghast, Access MVP

Hi Michel.
Your response was brilliant! I like your logic and although you did
answer
the question, I didn't phrase my question correctly. I actually need to
return top 30 records for each day.....sorry!!!

Hi,

Make a query that does the count


SELECT Field2, Date1, COUNT( ID ) As myCount
FROM myTable
GROUP BY Field2, Date1


Save it as, say, q1.


Next, make the following:


SELECT Date1, MAX(myCOUNT) As myMax
FROM q1
GROUP BY Date1

Save it as, say q2.

Finally,


SELECT q1.*
FROM q1 INNER JOIN q2
ON q1.Date1= q2.Date1 AND q1.MyCount=q2.MaxCount




The first query computes the counts (per Date1, per Field2). The second
query find the max of the counts, per day, the third query picks up the
whole records in q1 that "matches" the max found, per date, in the
second
query.




Hoping it may help,
Vanderghast, Access MVP

Help. I need to return the top records (based on highest Count) by
day....any suggestions? This is what I have so far:

SELECT Date1, Field2, Count([ID])
FROM Table1
GROUP BY Date1, Field2
ORDER BY Date1, Count([ID]) DESC