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
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.
Select top N rows for each group
If there were a constant number per group, you could do:
select i.*
from items as i inner join
groups as g
on i.group_id = g.id
where i.id in (select top 2 i2.id
from items i2
where i2.group_id = i.group_id
order by i2.score desc
);
Instead, you will need to enumerate the values and this is expensive in MS Access:
select i.*
from (select i.*,
(select count(*)
from items i2
where i2.group_id = i.group_id and
(i2.score < i.score or
i2.score = i.score and i2.id <= i2.id
)
) as seqnum
from items as i
) as i inner join
groups as g
on i.group_id = g.id
where i.seqnum <= g.top_count;
This logic implements the equivalent of row_number()
, which is the right way to solve this problem (if your database supports it).
Related Topics
Update Multiple Rows in Same Query Using Postgresql
How to Join Multiple SQL Tables Using the Ids
How to Get Difference Between Two Rows For a Column Field
How to Return Multiple Values in One Column (T-Sql)
Custom Serial/Autoincrement Per Group of Values
Hive Select Count(*) Non Null Returns Higher Value Than Select Count(*)
How to Find Gaps in Sequential Numbering in MySQL
Possible to Perform Cross-Database Queries With Postgresql
Doing a Where .. in Subquery in Doctrine 2
Add Foreign Key Relationship Between Two Databases
How to Roll Back Create Table and Alter Table Statements in Major SQL Databases
Performing SQL Queries on an Excel Table Within a Workbook With Vba Macro
When to Use Common Table Expression (Cte)
Calculating Difference Between Two Timestamps in Oracle in Milliseconds
How to Create a Step in My SQL Server Agent Job Which Will Run My Ssis Package