Mysql sort results by condition

MySQLorder-by

I have the following scenario:

table:

    +----+-----------+
    | id | name      |
    +----+-----------+
    | 1  | zx name1  |
    | 2  | zx name7  |
    | 3  | zx name3  |
    | 4  | name5     |
    | 5  | name2     |
    | 6  | name3     |
    | 7  | zx name4  |
    +----+-----------+

I want to order the results like this:

first results will be those having zx in name ASC
second the rows that does not have zx in name in order of id

The ordered result set should look like this:

    +----+-----------+
    | id | name      |
    +----+-----------+
    | 1  | zx name1  |
    | 3  | zx name3  |
    | 7  | zx name4  |
    | 2  | zx name7  |
    | 4  | name5     |
    | 5  | name2     |
    | 6  | name3     |
    +----+-----------+

I tried the following queries:

    select id, name from table
    where id < 100
    order by (name like 'zx%'), id


    select id, name from table
    where id < 100
    order by case when name like 'zx%' then name else id end

and many variations of this but with no luck.

Can you please give me some hints

Thank you

Best Answer

You need to separate the 3 ordering cases:

whether the column starts with 'zx' or not
order by name for the first group
order by id for the second group

Then because the first one needs a case expression, you need to use the same case expression in the following two, with the different orders and null accordingly:

order by 
    case when name like 'zx%' then 0 else 1 end,        -- 1st ordering column
    case when name like 'zx%' then name else null end,  -- 2nd
    case when name like 'zx%' then null else id end     -- 3rd

The 1st ensures that all rows that match the like 'zx%' condition will be first and the rows that don't match afterwards.

The 2nd column will be used for ordering by name the first group (rows that match) and ignored for the second group (rows that don't match) because it will be null for all of them.

The 3rd column will be ignored for the first group (rows that match) as it will be null and the rows will already have been ordered anyway by the 1st and 2nd column. But ti will be used for ordering by id the second group (rows that don't match).

Related Solutions

MySQL : Conditional ORDER BY to only one column

SELECT* FROM mytable ORDER BY
LOCATE(CONCAT('.',`group`,'.'),'.9.7.6.10.8.5.');

I took your sample data, loaded it into a table called mytable and ran it.

Here are the results:

mysql> use test
Database changed
mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.04 sec)

mysql> create table mytable
    -> (
    ->    names varchar(10),
    ->    `group` int
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into mytable values
    -> ('susanita',  10),
    -> ('miguelito', 5),
    -> ('mafalda',   7),
    -> ('manolito',  8),
    -> ('libertad',  6),
    -> ('felipe',    9),
    -> ('guille',    8);
Query OK, 7 rows affected (0.09 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+-----------+-------+
| names     | group |
+-----------+-------+
| susanita  |    10 |
| miguelito |     5 |
| mafalda   |     7 |
| manolito  |     8 |
| libertad  |     6 |
| felipe    |     9 |
| guille    |     8 |
+-----------+-------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM mytable ORDER BY
    -> LOCATE(CONCAT('.',`group`,'.'),'.9.7.6.10.8.5.');
+-----------+-------+
| names     | group |
+-----------+-------+
| felipe    |     9 |
| mafalda   |     7 |
| libertad  |     6 |
| susanita  |    10 |
| manolito  |     8 |
| guille    |     8 |
| miguelito |     5 |
+-----------+-------+
7 rows in set (0.01 sec)

mysql>

Give it a Try !!!

UPDATE 2011-09-06 12:33 EDT

Here is another approach:

SELECT* FROM mytable ORDER BY
IF(FIELD(`group`,9,7,6,10,8,5)=0,99999,FIELD(`group`,9,7,6,10,8,5));

This will force any groups other than 9,7,6,10,8,5 to appear at the very bottom of the query.

UPDATE 2011-09-06 14:39 EDT

mysql> SELECT names, `group`
    -> FROM mytable
    -> WHERE `group` IN (9,7,6,10,8,5)
    -> ORDER BY find_in_set(`group`,'9,7,6,10,8,5');
+-----------+-------+
| names     | group |
+-----------+-------+
| felipe    |     9 |
| mafalda   |     7 |
| libertad  |     6 |
| susanita  |    10 |
| manolito  |     8 |
| guille    |     8 |
| miguelito |     5 |
+-----------+-------+
7 rows in set (0.00 sec)

Hey @Nick, yours works as well against my sample data !!!

MySQL order by – one column conditional on another column

How about...

ORDER BY (`status` = 'complete'), `priority`

The expression status = 'complete' would resolve to 0 for non-complete, and 1 for complete... exactly the order you want... so you'd get all of the non-completed items sorted by priority, followed by all the completed items sorted by priority.

Best Answer

Related Solutions

MySQL : Conditional ORDER BY to only one column

MySQL order by – one column conditional on another column

Related Question