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 · Accepted Answer · 2017-12-05 10:34:09Z

1

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)

answered Dec 5, 2017 at 10:34

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql : match against and having query with max

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related