Ms Access Select Top N Query Grouped by Multiple Fields

MS Access Select top n query grouped by multiple fields

You can do something like this

SELECT StudentID, Year, Subject,  AVG(TestScore) AS AvgScore
  FROM
(
  SELECT StudentID, Year, Subject, TestScore
   FROM MyTable t
   WHERE TestID IN
  (
   SELECT TOP 3 TestID 
     FROM MyTable
    WHERE StudentID = t.StudentID
      AND Year = t.Year
      AND Subject = t.Subject
    ORDER BY TestScore DESC, TestID
  )
) q
 GROUP BY StudentID, Year, Subject
 ORDER BY StudentID, Year, Subject;

Sample output:

| STUDENTID | YEAR | SUBJECT | AVGSCORE |
|-----------|------|---------|----------|
|         1 | 2012 |       1 |       91 |
|         1 | 2012 |       2 |       84 |
|         2 | 2012 |       1 |       94 |
|         2 | 2012 |       3 |       95 |

Here is SQLFiddle demo.

Demo as usually is for SQL Server but expected to work in MS Access, maybe with minor syntactic tweaks

Top n records per group sql in access

I had a similar problem a year ago: Top 3 per group including 0

Using the same approach, this will return the latest three dates for each LoginID - you may get more than three records if there are tied dates for the same LoginID.

SELECT  PR1.LogInID, PR1.Score, PR1.[Date Taken]
FROM    Progress AS PR1
WHERE   PR1.[Date Taken] IN (
                        SELECT TOP 3 PR2.[Date Taken]
                        FROM    Progress PR2
                        WHERE   PR2.LoginID = PR1.LoginID
                        ORDER BY PR2.[Date Taken] DESC
                        )
ORDER BY    LoginID, [Date Taken]

Access top n in group

This should work for you. If it doesn't satisfy your requirements, post back what you need.
Your original desire was to have 25, so you'd simply modify the last clause to be HAVING COUNT(*) <= 25

SELECT  a.item, 
        a.category, 
        a.inventorycount, 
        COUNT(*) AS ranknumber
FROM inv AS a 
INNER JOIN inv AS b 
     ON (a.category = b.category) 
     AND (a.inventorycount <= b.inventorycount)
GROUP BY  a.category, 
          a.item, 
          a.inventorycount
HAVING COUNT(*) <= 2
ORDER BY a.category, COUNT(*) DESC

If you wanted to select more columns from the table, simply add them to the SELECT and `GROUP BY' clauses.

Only when you want to expand the "TOP n for each Category, foo, bar", then you would add those columns to the INNER JOIN clause as well.

--show the top 2 items for each category and year.
SELECT  a.item, 
        a.category, 
        a.year,
        a.inventorycount, 
        COUNT(*) AS ranknumber
FROM inv AS a 
INNER JOIN inv AS b 
     ON (a.category = b.category) 
     AND (a.year = b.year) 
     AND (a.inventorycount <= b.inventorycount)
GROUP BY  a.category, a.item, a.year, a.inventorycount
HAVING COUNT(*) <= 2
ORDER BY a.year, a.category, COUNT(*) DESC

SQL - Select top n grouped by multiple fields, ordered by count

Similarly to this question, define a CTE that computes all device counts for your desired groups, then use it in a WHERE ... IN subquery, limited to the top 2 devices for that date:

WITH device_counts AS (
  SELECT did, date(dateval) AS dateval, COUNT(*) AS device_count
  FROM data
  GROUP BY did, date(dateval)
)
SELECT did, date(dateval) FROM device_counts DC_outer
WHERE did IN (
  SELECT did
  FROM device_counts DC_inner
  WHERE DC_inner.dateval = DC_outer.dateval
  GROUP BY did, date(dateval)
  ORDER BY DC_inner.device_count DESC LIMIT 2
)
ORDER BY date(dateval), did

MS Access get top 2 rows from table when grouped and ordered

You don't need the subquery, but I think your query should work.

Here is a somewhat simpler version:

SELECT Clinical_id, Clinical_desc, Doctor,
       COUNT(Doctor) AS Count,
       AVG(Price) AS Avg_price,
       AVG(Cost) AS Avg_cost
FROM Summary_Data as sd
WHERE sd.Doctor IN (SELECT TOP 2 sd2.Doctor
                    FROM Summary_Data as sd2
                    WHERE sd2.Clinical_id = sd.Clinical_id
                    GROUP BY sd2.Doctor
                    ORDER BY COUNT(sd2.Doctor) DESC, SUM(sd2.Cost) DESC,
                             sd2.Doctor  -- Just added to keep the ordering unique so 2 rows are always returned
                   )
GROUP BY sd.Clinical_id, sd.Clinical_desc, sd.Doctor
ORDER BY COUNT(Doctor) DESC, SUM(Cost) DESC;

---

Related Topics