MySQL - How to ORDER BY RELEVANCE? INNODB Table
19,732
version 1:
SELECT max(id) id, name
FROM cards
WHERE name like '%John%'
GROUP BY name
ORDER BY CASE WHEN name like 'John %' THEN 0
WHEN name like 'John%' THEN 1
WHEN name like '% John%' THEN 2
ELSE 3
END, name
version 2:
SELECT max(id) id, name
FROM cards
WHERE name like '%John%'
GROUP BY name
ORDER BY CASE WHEN name like 'John%' THEN 0
WHEN name like '% %John% %' THEN 1
WHEN name like '%John' THEN 2
ELSE 3
END, name
Author by
k00k
Updated on June 03, 2022Comments
-
k00k almost 2 years
I've got about 20,000 rows in an INNODB table called 'cards', so FULLTEXT is not an option.
Please consider this table:
id | name | description ---------------------------------------------------------- 1 John Smith Just some dude 2 Ted Johnson Another dude 3 Johnathan Todd This guy too 4 Susan Smith Her too 5 Sam John Bond And him 6 John Smith Same guy as num 1, another record 7 John Adams Last guy, promise
So, say the user searches for 'John', I want the result set to be in the order of:
7 John Adams 6 John Smith 3 Johnathan Todd 5 Sam John Bond 2 Ted Johnson
Please note that we've only pulled 'John Smith' once, we took his most recent entry. Due to my data, all names are for the same exact person, no need to worry about 2 different guys named John Smith. Ideas? Let me know if I can clarify anything.
-
k00k over 14 yearsAwesome! That works exactly like I wanted it to. Thanks najmeddine!
-
manji over 14 yearsglad to help. I modified it a little bit to handle more cases.
-
k00k over 14 yearsI think the other way works better in my specific case, but they both may be helpful to others, can you put both up there for posterity's sake?
-
Rafael Herscovici over 12 yearsanyone know how can i do this with parametized values ?
-
worenga about 12 yearsYou'll need to carry the % into the Parameters
-
Xunnamius about 11 years+1 beautiful quasi-solution to Innodb's lack of natural language search in the old version of mysql I'm stuck in.
-
Joe Jankowiak over 7 yearsCould you put what the difference between the two versions is? I tried both and while they work, they return the same results for me.