select rand() from subquery

0
votes

I have apparently simple query:

SELECT tag_id 
from books_tags 
WHERE tag_id IN (SELECT tag_id 
                 FROM books_tags 
                 GROUP BY tag_id 
                 ORDER BY COUNT(tag_id) DESC 
                 LIMIT 100) 
ORDER BY RAND() 
LIMIT 1

I wish to select one random record from query inside. It does not work, error says that LIMIT and ORDER cannot appear in subquery.

I dont wanna create temporary table, or random on servers side. Any ideas how to merge it into wone query?

1
sqlrandomsubquery

1 Answers

0
votes

You should be able to do the same with INNER JOIN / LEFT OUTER JOIN

SELECT a.tag_id 
from books_tags a
INNER JOIN  (SELECT tag_id 
                 FROM books_tags 
                 GROUP BY tag_id 
                 ORDER BY COUNT(tag_id) DESC 
                 LIMIT 100) b ON a.tag_id = b.tag_id
ORDER BY RAND() 
LIMIT 1