ORDER BY two fields. Second field (int) orders as varchar problem

Question

Have been breaking my head over this for 2 hours now, i thought this would be simple. I have a table with a varchar value (task_group) and a int value (task_group_weight). I would like the table sorted by task_group first and by task_group_weight after that.

My Query is:

SELECT
    `id`,
    `task_group`,
    `task_group_weight` 
FROM
    `cron` 
WHERE
    `task_group` LIKE 'Imap%' 
ORDER BY
    `task_name`,
    `task_group_weight`;

I expect the output to be:

|id |task_group | task_group_weight
|1  |imaprunner | 1
|2  |imaprunner | 2
|3  |imaprunner | 10

Instead im getting:

|id |task_group | task_group_weight
|1  |imaprunner | 1
|2  |imaprunner | 10
|3  |imaprunner | 2

So in the second part of the order by it is ordering it like a varchar and not like a int. I already tried the following:

  ORDER BY `task_name`,`task_group_weight`+0;
  ORDER BY `task_name`, CAST(`task_group_weight` AS UNSIGNED);
  ORDER BY `task_name`, LPAD(`task_group_weight`,5,0);

All with the same result. Hope that someone can give me a quick pointer!

Answer 1

Ref function ABS of mysql

ORDER BY task_group, ABS(task_group_weight)

You have task_group in select & task_name in order by. Please check adding task_group in order by.