CODEWITHSUNDEEP
sqlrandom
Christian Hagelid
Christian Hagelid

Reputation: 8355

How to select a set number of random records where one column is unique?

I've been struggling with this one SQL query requirement today that I was wondering if someone could help me with.

I have a table of sports questions. One of the columns is the team related to the question. My requirement is to return a set number of random questions where the teams are unique.

So lets say we have the following table and want 5 questions:

Question        Answer        Team
-----------------------------------
question 1      answer 1      team A
question 2      answer 2      team B
question 3      answer 3      team B
question 4      answer 3      team D
question 5      answer 3      team A
question 6      answer 3      team C
question 7      answer 3      team F
question 8      answer 3      team C
question 9      answer 3      team G
question 10     answer 3      team D

A valid result would return:

question 1      answer 1      team A
question 2      answer 2      team B
question 4      answer 3      team D
question 6      answer 3      team C
question 7      answer 3      team F

I feel that it should be possible to accomplish this as a clean SQL statement with some clever use of Distinct and Take but I haven't been able to get it right yet.

Best solution so far is from Mladen Prajdic. I have just updated it slightly to improve on it's randomness:

SELECT TOP 10 * 
FROM    (SELECT ROW_NUMBER() OVER(PARTITION BY Team ORDER BY Team, NEWID()) AS RN, *
    FROM Question
    ) teams
WHERE   RN = 2
ORDER BY NEWID()

Upvotes: 0

Views: 1642

Answers (3)

pilif
pilif

Reputation: 12718

In PostgreSQL (which has distinct on), I'd probably do something like this:

select distinct on (Team) Question, Answer, Team from test order by Team, random() limit 5;

just tested it. Seems to work.

Upvotes: 0

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

for sql 2005 you can do this:

select top 5 * 
from    (
            select ROW_NUMBER() over(partition by team order by team) as RN, *
            from @t 
        ) t
where RN = 1
order by NEWID()

Upvotes: 2

William
William

Reputation: 801

This should do what you need, in oracle; for a different database you'll need to use their random number source, obviously. There's probably a better way; lets hope someone else will point it out to us :p

select question, answer, team
from
(
select question, answer, team, r
from
(
select 
    question, 
    answer, 
    team,
    rank() over (partition by team order by dbms_random.value) r 
from questions
)
where r = 1
order by dbms_random.value
) where rownum<=5;

Test code:

create table questions(question varchar2(16), answer varchar2(16), team varchar2(16));

insert into questions(question, answer, team)
values ('question 1',      'answer 1',      'team A');

insert into questions(question, answer, team)
values ('question 2',      'answer 2',      'team B');

insert into questions(question, answer, team)
values ('question 3',      'answer 3',      'team B');

insert into questions(question, answer, team)
values ('question 4',      'answer 3',      'team D');

insert into questions(question, answer, team)
values ('question 5',      'answer 3',      'team A');

insert into questions(question, answer, team)
values ('question 6',      'answer 3',      'team C');

insert into questions(question, answer, team)
values ('question 7',      'answer 3',      'team F');

insert into questions(question, answer, team)
values ('question 8',      'answer 3',      'team C');

insert into questions(question, answer, team)
values ('question 9',      'answer 3',      'team G');

insert into questions(question, answer, team)
values ('question 10',    'answer 3',      'team D');

commit;

Upvotes: 1

Related Questions