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
The DISTINCT
s 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;