CODEWITHSUNDEEP
distinct
Abdulsattar Mohammed
Abdulsattar Mohammed

Reputation: 10722

How can a query return only one random row for each distinct column?

I've a table with two columns, say, column1 and column2. Column2 is not unique. For each distinct value of column2, I want a random row, only one row, from the table?

i.e. my result set should have as many rows as the number of distinct values of column2.

eg:

column1   column2
x         1
y         2
z         1

I want the result to be

column1   column2
x         1
y         2

or 

column1   column2
z         1
y         2

Is this possible using only SQL?

Upvotes: 0

Views: 1314

Answers (3)

tom
tom

Reputation: 2077

This query should do the trick on MySQL (tested on MySQL 5):

select a.column1, a.column2 from (select * from foo b order by rand()) a group by a.column2;

Upvotes: 1

pierre
pierre

Reputation: 1245

You might want to try something like ROW_NUMBER() OVER(ORDER BY column1 asc) to pull back your row numbers and filter it that way.

You didnt mention what flavour of SQL you use and I'm recalling this from memory - but this kind of thing works in Oracle 9.

Upvotes: 0

jitter
jitter

Reputation: 54605

SELECT MAX(column1), column2 FROM yourtablename GROUP BY column2

Upvotes: 1

Related Questions