1

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

1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.