mysql : match against and having query with max

2018-06-26 22:39:46

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?

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)

链接地址: http://www.djcxy.com/p/75340.html

上一篇: MySQL FULLTEXT无法正常工作

下一篇: mysql：与max进行匹配并进行查询