Mysql sort results by condition 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 &lt; 100
    order by (name like 'zx%'), id


    select id, name from table
    where id &lt; 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
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).