CODEWITHSUNDEEP
mysql
Sugumar Venkatesan
Sugumar Venkatesan

Reputation: 4028

mysql : match against and having query with max

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?

Upvotes: 0

Views: 455

Answers (1)

juergen d
juergen d

Reputation: 204746

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)

Upvotes: 1

Related Questions