Return 10 rows per person from Access table

Vote count:

0

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

asked 1 min ago

rryanp

357

Return 10 rows per person from Access table