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

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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;