For some reason i cant manage to sort results 1st by empty string and then by id DESC, its look like ORDER BY url ASC is sorting by length of string .

How to tell query to sort by empty not by length and than by id desc ?

EXAMPLE LINK

SELECT * FROM r WHERE r.type IN('product','manual_change') ORDER BY url ASC, id DESC


        CREATE TABLE `r` (
      `id` int(11) NOT NULL,
      `id_type` int(11) NOT NULL,
      `type` enum('category','product','manufacturer','manual_change') NOT NULL,
      `redirect_type` tinyint(1) NOT NULL,
      `url` varchar(300) NOT NULL,
      `link_from` varchar(300) NOT NULL COMMENT 'manual change, use url as LINK_TO'
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8;



    INSERT INTO `r` (`id`, `id_type`, `type`, `redirect_type`, `url`, `link_from`) VALUES
    (38, 0, 'manual_change', 0, '', ''),
    (39, 0, 'manual_change', 0, 'example1.com', ''),
    (29, 27406, 'product', 0, 'example11.com', ''),
    (42, 0, 'manual_change', 0, 'example111.com', ''),
    (41, 27339, 'product', 0, 'example1111.com', ''),
    (24, 27382, 'product', 0, 'example1111.com', ''),
    (43, 0, 'manual_change', 0, 'example.com', ''),
    (22, 27352, 'product', 0, 'example.com', ''),
    (20, 27319, 'product', 0, 'example.com', ''),
    (19, 27322, 'product', 0, 'example.com', '');

CodePudding user response：

You can order by case:

SELECT * 
FROM r 
WHERE r.type IN('product','manual_change') 
ORDER BY case when r.url='' then r.url end desc,id desc ;

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=9273bc6070ce3b278e2f65d6f791d1c9

I suggest better using null rather than empty strings