lundi 29 mars 2021

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



Aucun commentaire:

Enregistrer un commentaire