Geek Answers Handbook

Ordering rows in a MySQL row without specific sorting?

Let's say I have the following results:

Mackay Mackay Airport Melbourne Melbourne Airport Sydney Sydney Ac Sydney Airport

How can I make them booked at the airport always upstairs? In alphabetical order, for example:

 Mackay Airport Mackay Melbourne Airport Melbourne Sydney Airport Sydney Sydney Ac

It is a little confusing how to make airports more visible.

+6
source share
3 answers

Not sure if the following query covers all cases, but seems to work with your example data:

 select name, SUBSTRING_INDEX(name,'Airport',1) as l, LOCATE('Airport',name) as r from ( select 'Sydney Airport' as name union all select 'Sydney' union all select 'Sydney Ac' union all select ' Mackay Airport' union all select 'Mackay' union all select 'Melbourne' union all select 'Melbourne Airport' )a order by l asc, r desc

with a table it will look like

 select name from table1 ORDER BY SUBSTRING_INDEX(name,'Airport',1) ASC, LOCATE('Airport',name) DESC
+2
source

It seems that you cannot order an integer and a string at the same time, but you can get a string for ordering based on whether there is an "airport". This is stupid, but it works:

 ORDER BY IF(name NOT LIKE '% Airport', CONCAT(name, ' Birport'), name)
+1
source

The following query will give you the expected result (even if the query is complex).

 SELECT t.name FROM ( SELECT @num := IF(@num IS NULL, 1, @num+1) AS number, name FROM places ORDER BY IF ((name LIKE '% Airport'), SUBSTRING(name, 1, LOCATE(' Airport', name) - 1), name) DESC) t ORDER BY t.number DESC;

Here is an example.

http://www.sqlfiddle.com/#!2/117b7/80

0
source

More articles:


All Articles