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
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
how to select top N in access report detail section or select top N in sql subquery
Try the following query as your report recordsource:
SELECT TestTable.GroupByCol, TestTable.DetailColA
FROM TestTable
WHERE TestTable.DetailColA IN
(SELECT TOP 2 DetailColA FROM TestTable AS TT
WHERE TT.GroupByCol=TestTable.GroupByCol
ORDER BY TT.DetailColA)
ORDER BY TestTable.GroupByCol, TestTable.DetailColA
See Allen Browne's Subquery Basics for more information.
EDIT: As you alluded to in your comment, this can also be written as follows:
SELECT a.GroupByCol, a.DetailColA, COUNT(*) As RankNumber
FROM TestTable AS a INNER JOIN TestTable AS b
ON a.GroupByCol = b.GroupByCol
AND a.DetailColA >= b.DetailColA
GROUP BY a.GroupByCol, a.DetailColA
HAVING COUNT(*)<=2
ORDER BY a.GroupByCol, a.DetailColA, COUNT(*)
You would have to benchmark to be sure, but this second version is probably more efficient. Which is more readable is probably a matter of personal preference (I find the first version more intuitive and readable).
select top x by group in ms access
MsAccess can have a subquery as its select expression, which can be used to produce a limited rownumber:
SELECT *
FROM (
SELECT TblA.*,
(SELECT count(*) from Sheet1 TblB
where tblB.GROUP=tblA.GROUP and tblB.Index<=tblA.Index) as ROWNO
FROM Sheet1 TblA) Step1
where Step1.ROWNO<=Step1.A
Biggest caveat is that if two rows of the same group can have the same Index value.
Related Topics
Postgresql - Replace HTML Entities
Limit Ignored in Query with Group_Concat
Insert Deleted Values into a Table Before Delete with a Delete Trigger
Creating User with Password from Variables in Anonymous Block
SQL Server Cte Parent Child Recursive
How to Get Specific Xml Namespace in Xquery in SQL Server
Sqlite: How to Select "Most Recent Record for Each User" from Single Table with Composite Key
Oracle - Literal Does Not Match Format String Error
Concat Group by in Vertica SQL
Create a Table of Two Types in Postgresql
Query JSONb Column Containing Array of JSON Objects