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