MySQL - How to ORDER BY RELEVANCE? INNODB Table

Question

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.

Metts

You seem to want to order by "Lastname, firstname", not by relevance (however you define that). Is this correct? – Fivefinger

Answer 1

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

Wye

Awesome! That works exactly like I wanted it to. Thanks najmeddine! – Metts

glad to help. I modified it a little bit to handle more cases. – Wye

I 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? – Metts

anyone know how can i do this with parametized values ? – Sindysine

You'll need to carry the % into the Parameters – Hargrove

+1 beautiful quasi-solution to Innodb's lack of natural language search in the old version of mysql I'm stuck in. – Unscrupulous

Could you put what the difference between the two versions is? I tried both and while they work, they return the same results for me. – Fidelis