CODEWITHSUNDEEP
mysql
jessica
jessica

Reputation: 1687

Having two different order, and limit statements in SQL

I have a select statement, where I need to first order it by relevance, get the top ten relevance, order it by rand(), and then limit it by 1, but I don't think it's possible to do that with 2 different order, and limit statements. So, my question is, is there a workaround this problem? Thanks.

SELECT id, input, response, 
MATCH (
input
)
AGAINST (
 'cat frogs trees'
IN NATURAL LANGUAGE
MODE
) AS relevance
FROM allData
HAVING relevance >0
ORDER BY relevance DESC
LIMIT 10
ORDER BY RAND()
LIMIT 1

Upvotes: 2

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

Use a subquery:

SELECT t.*
FROM (SELECT id, input, response, 
             MATCH (input) AGAINST ('cat frogs trees' IN NATURAL LANGUAGE MODE) AS relevance
      FROM allData
      HAVING relevance > 0
      ORDER BY relevance DESC
      LIMIT 10
     ) t
ORDER BY RAND()
LIMIT 1;

Upvotes: 2

Related Questions