CODEWITHSUNDEEP
mysqlsqlsql-order-by
Nick Van Brunt
Nick Van Brunt

Reputation: 15474

Like and equal SQL order by equal?

Say I have the following table:

ID      Author
---------------------
1       Bill
2       Joe
3       Bill and Joe
4       Bill

And I want a result set that would come from:

SELECT id FROM table WHERE Author LIKE '%Bill%' OR Author = 'Bill'

How could I order it so that the rows matched with equality are the first rows returned and the like matches comes after? e.g. The query would return 1,4,3. I am using MySQL but would take answers in any DB.

Upvotes: 1

Views: 1280

Answers (4)

Xavier
Xavier

Reputation: 1

If that test needs to be expanded to several variables, I have expanded a bit Martin's answer in the following manner (for instance on 3 criteria): 

SELECT  id
FROM    YourTable
WHERE  Author LIKE '%Bill%' 
ORDER BY 
(CASE WHEN criteria_1='target_1' THEN 1 ELSE 2 END) 
*
(CASE WHEN criteria_2='target_2' THEN 1 ELSE 2 END) 
*
(CASE WHEN criteria_3='target_3' THEN 1 ELSE 2 END)

Best regards (and many thanks to Martin),

Upvotes: 0

Andrej
Andrej

Reputation: 7504

I have tested your data in db and ORDER BY works as you wish without any problems but you can use the following query:

New query:

select Author, CONCAT(Author, ' ') as text_n 
from table where Author like '%Bill%' order by text_n;

Your edited query:

SELECT id FROM table WHERE Author LIKE '%Bill%' order by Author;

Upvotes: -1

Nicola Cossu
Nicola Cossu

Reputation: 56357

SELECT id 
FROM table 
WHERE Author LIKE '%Bill%'
order by if(author='Bill',1,2)

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453298

SELECT  id
FROM    YourTable
WHERE  Author LIKE '%Bill%' 
ORDER BY CASE WHEN Author = 'Bill' THEN 0
              ELSE 1
         END

You can probably just do ORDER BY Author != 'Bill' as well - Not sure about ordering by boolean expressions in MySQL.

Upvotes: 8

Related Questions