SQL return n rows per row value

Question

Greetings SQL people of all nations.

Simple question, hopefully a simple answer.

I have an Oracle database table with persons' information. Columns are:

FirstName, LastName, BirthDate, BirthCountry

Let's say in this table I have 1500 persons born in Aruba (BirthCountry = "Aruba"), 678 Botswanans (BirthCountry = "Botswana"), 13338 Canadians (BirthCountry = "Canadia").

What query would I need to write extract a sample batch of 10 records from each country? It doesn't matter which 10, just as long as there are 10.

This one query would output 30 rows, 10 rows from each BirthCountry.

score 4 · Accepted Answer · answered May 06 '13 at 13:27

4

This will select 10 youngest people from each country:

SELECT  *
FROM    (
        SELECT  p.*,
                ROW_NUMBER() OVER (PARTITION BY birthCountry ORDER BY birthDate DESC) rn
        FROM    persons p
        )
WHERE   rn <= 10

answered May 06 '13 at 13:27

Quassnoi

413,100
91
616
614

+1, are you sure you can use `*` and `row_number` in the same select? – Andomar May 06 '13 at 13:32
@Andomar: as long as you alias the `*`, yes – Quassnoi May 06 '13 at 13:33
Works very well. Exactly what I was after. Thanks again. – Nimbocrux May 07 '13 at 08:30

score 1 · Answer 2 · answered May 06 '13 at 13:32

This would pick ten random persons, different ones each time you run the query:

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

SQL return n rows per row value

2 Answers2

Linked