Return 10 rows per person from Access table

Question

I have a table that includes ID, person's name, and then some other data. I'm trying to write a query that returns only 10 rows for each person (ideally 10 random rows, but if it has to be the first 10 rows for each person, that would suffice). I've found SQL to return 10 random rows from the table, but not 10 rows per person. This tells how to solve it in Oracle, but I'm having trouble making that work in Access: SQL return n rows per row value

select  *
from    (
        select  row_number() over (partition by BirthCountry 
                                   order by dbms_random.value) as rn
        ,       FirstName
        ,       LastName
        ,       BirthDate
        ,       BirthCountry
        from    YourTable
        )
where   rn <= 10

EDIT: I have now tried this based on the link posted to the comments, and it only returns one record (for the person who just has one record in the table--all the others would have more than 10).

SELECT a.ID, a.mapped_prov_name, Count(*) AS rank_number
FROM qryJustNames AS a
INNER JOIN qryJustNames AS b ON a.mapped_prov_name = b.mapped_prov_name
GROUP BY a.ID, a.mapped_prov_name
HAVING COUNT(*) <= 10
ORDER BY a.mapped_prov_name

Try http://stackoverflow.com/questions/3481916/access-top-n-in-group — Fionnuala, Dec 02 '14 at 02:11
Wow, thanks @Fionnuala--I searched for so long and somehow never saw that. Unfortunately it's not working for me though, and I can't figure out what I'm doing wrong. All names have at least 10 records except for one (that just has one record), and when I run my query, I just get that one person returned. I have edited the post to show what I tried...any thoughts would be greatly appreciated! — rryanp, Dec 02 '14 at 04:20

score 0 · Answer 1 · edited May 23 '17 at 10:33

0

The following code returns 10 random rows of data for each name it can find.

SELECT yt.id, yt.name, yt.data
FROM yourtable yt
WHERE id IN
(
SELECT TOP 10 id
FROM yourtable
WHERE name = yt.name
ORDER BY rnd(yt.id)
)
ORDER BY id, name

I've found the answer here: MS Access Select top n query grouped by multiple fields just modified it to your needs.

Kind regards,

Wouter

edited May 23 '17 at 10:33

Community

1
1

answered Dec 02 '14 at 08:08

Wouter

612
5
14

Return 10 rows per person from Access table

1 Answers1