CODEWITHSUNDEEP
mysqlsqlsortingsql-order-by
Omid Naji
Omid Naji

Reputation: 147

Custom Sort By "%%" in mysql

I have a table like this:

|city_name|
 Shadyside
 Chalkyitsik
 Wyalusing
 Quartzsite
 Seaside
 Shawnee Mission
 Siberia
 Sibley
 Nicasio
 Lacassine
 Sicily Island
 Andalusia
 Sidell
 Sidney
 ...
 ..
 .

And selected with the following queries:

SELECT city_name FROM my_table WHERE city_name LIKE '%si%'

Instead of '%si%', anything can be put.

I want to sort by the words first started with 'si'.

And the output is like this:

|city_name|
 Siberia
 Sibley
 Sicily Island
 Sidell
 Sidney
 ... And the rest of the words that are '%si%'

How should this sorting(ORDER BY) be done?

Upvotes: 1

Views: 38

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use multiple keys in the order by and expressions too:

SELECT city_name
FROM my_table
WHERE city_name LIKE '%si%'
ORDER BY (city_name LIKE 'si%') DESC, city_name;

MySQL treats boolean expressions as numbers in a numeric context, with "0" for false and "1" for true. The DESC puts the matches (1 = true) first.

Upvotes: 3

always007
always007

Reputation: 50

SELECT city_name
FROM my_table
WHERE city_name LIKE '%si%'
ORDER BY (city_name LIKE 'si%') DESC;

Upvotes: 2

Related Questions