Search code examples
mysqlrandomdistinct

MySQL distinct first column, random second column


I need to select distinct values from column A in the database, and then randomise the result associated with column A in column B.

In the below example:

Year    Filename
1973    1.jpg
1973    2.jpg
1973    3.jpg
1973    4.jpg
1975    5.jpg
1975    6.jpg
1975    7.jpg
1975    8.jpg

The result I'm looking for would be to always show the distinct values in Column A, and then randomly select a value associated with that from Column B.

So first page load might produce result:

Year    Filename
1973    1.jpg
1975    5.jpg

But refresh page load might then produce result:

Year    Filename
1973    3.jpg
1975    8.jpg

1973 and 1975 will always each appear once, but what the second field is will vary with every page load.

This is my attempt but it is executing around 1 second:

SELECT DISTINCT year, (SELECT DISTINCT filename from photogallery WHERE year = year ORDER BY rand() LIMIT 1) as filename FROM photogallery GROUP BY year ORDER BY 1

Solution

  • The DISTINCTs are superfluous and you must use table aliases so that the engine knows the table the year column belongs to. Right now the WHERE clause in the inner query is as good as none as it's always true.

    SELECT pg1.year,
          (SELECT pg2.filename
                  FROM photogallery pg2
                  WHERE pg2.year = pg1.year
                  ORDER BY rand()
                  LIMIT 1) AS filename
          FROM photogallery pg1 
          GROUP BY pg1.year
          ORDER BY 1;