mysql : match against and having query with max

Question

I have the following query

SELECT *,MATCH(title,text) AGAINST('my home' in boolean mode) as relevance FROM blog where active = 1

The above query returns 5 rows with relevance 2,2,0,0,0

Now I want to select only the rows with maximum relevance. So it should return only the rows which has 2 as their relevance.

I tried the following query.

SELECT *,MATCH(title,text) AGAINST('my home' in boolean mode) as relevance FROM blog where active = 1 having relevance=max(MATCH(title,text) AGAINST('my home' in boolean mode))

But it returns only one row..

Is there any way to get only that two rows?

juergen d juergen d · Accepted Answer · 2017-12-05T10:34:09

SELECT *, MATCH(title,text) AGAINST('my home' in boolean mode) as relevance 
FROM blog 
where active = 1
and MATCH(title,text) AGAINST('my home' in boolean mode) = (select max(MATCH(title,text) AGAINST('my home' in boolean mode)) from blog)

mysql : match against and having query with max

1 Answers