CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
Nimbocrux
Nimbocrux

Reputation: 519

SQL return n rows per row value

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.

Upvotes: 1

Views: 2206

Answers (2)

Andomar
Andomar

Reputation: 238296

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

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425833

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

Upvotes: 4

Related Questions