CODEWITHSUNDEEP
mysqlsqlsql-order-bywhere-clausesql-like
Andurit
Andurit

Reputation: 5762

MySQL - ORDER result based on how many ORs it match

databases are not my strength and I'm not sure if what I look for even exist. In general I would be happy for any advise which can lead me the right way.

What I've got:

I've got a query with multiple OR imagine it like something bellow: 
SELECT * FROM video WHERE video.name LIKE '%topspeed%' OR video.name LIKE '%%audi%';

Let's imagine result for this query gonna be:

+------------+---------------+
| id | name                  |
+------------+---------------+
|  1 | My best audi          |
|  2 | topspeed of mustang?  |
|  3 | audi A7 topspeed      |
|  4 | Do you like audi?     |
|  5 | topspeed of audi Q8   |
+------------+---------------+

What I need:

Is there a way to ORDER results based on how many ORs it match? So in other words I would expect the order from original example would be: 
+------------+---------------+
| id | name                  |
+------------+---------------+
|  5 | topspeed of audi Q8   |
|  3 | audi A7 topspeed      |
|  1 | My best audi          |
|  2 | topspeed of mustang?  |
|  4 | Do you like audi?     |
+------------+---------------+

Upvotes: 0

Views: 173

Answers (3)

Luuk
Luuk

Reputation: 14900

SELECT 
   id,
   name,
   (CASE WHEN video.name LIKE '%topspeed%' THEN 1 ELSE 0 END + 
    CASE WHEN video.name LIKE '%%audi%' THEN 1 ELSE 0 END)  as score
FROM video WHERE video.name LIKE '%topspeed%' OR video.name LIKE '%%audi%'
ORDER BY score DESC;

This query also works on MS-SQL, that's why I use CASE WHEN....END.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

In MySQL, you can do this:

SELECT v.*
FROM video v
WHERE v.name LIKE '%topspeed%' OR v.name LIKE '%audi%'
ORDER BY (v.name LIKE '%topspeed%') + (v.name LIKE '%audi%') DESC;

MySQL treats booleans like numbers in a numeric context, with 1 for "true" and 0 for "false".

Note two other changes. I added a table alias so the table is easier to reference when qualifying column names. I also changed '%%audi' to '%audi%'. Two '%'s in a row doesn't do anything that '%' does.

Upvotes: 2

GMB
GMB

Reputation: 222402

Consider:

ORDER BY 
    (video.name LIKE '%topspeed%') + (video.name LIKE '%audi%') DESC,
    id

Rationale: in numeric context, a MySQL conditional expression returns 1 if true, else 0. So videos that match on both conditions get a value of 2, and those that match on one only get 1. You can use a descending sort on that. I added another criteria to the ORDER BY clause, to get a deterministic sort by breaking the ties.

Upvotes: 1

Related Questions