I need one row for each textid. But it has to be chosen random, not the first in the MySQL table.

Shuffling like this does not work. It still takes the first row.

SELECT * FROM (
  SELECT * FROM textelements ORDER BY rand()
) AS z
GROUP BY z.textid;

Read the comments for my solution. I added LIMIT 1000000 to the subquery.

CodePudding user response：

WITH cte AS ( 
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY textid ORDER BY RAND()) rn
    FROM textelements 
)
SELECT *
FROM cte
WHERE rn = 1