MySQL - ORDER BY with part of UNION

Question

for long time i have used this method to order my lists (for html selects):

SELECT '' AS ID, '' AS Name UNION 
SELECT ID, Name FROM (SELECT ID, Name FROM myTable ORDER BY Name) AS myTableTmp

but after update to new MySQL(MariaDB) version i find out, this sorting is not working anymore. Like explained here it conflicts with SQL standart: mysql - order by inside subquery

What is best practice to get desired order? Or should i use easy way, and add LIMIT 65000 to nested query?

Edit:

Well, my original example was not precise enough, so people was able to misunderstood me. Here is corrected example:

CREATE TABLE IF NOT EXISTS `myTable` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(120) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;


INSERT INTO `myTable` (`ID`, `Name`) VALUES
(1, 'Banana'),
(2, 'Apple'),
(3, NULL);

If i select in old version:

SELECT '0' AS ID, '-' AS Name UNION 
SELECT ID, Name FROM (SELECT ID, Name FROM myTable ORDER BY Name) AS myTableTmp

I get:

0   -
3   NULL
2   Apple
1   Banana

How i get same result in new MySQL versions?

But i want first part of union stay on top no matter what. Will `SELECT '' AS ID, '' AS Name UNION SELECT ID, Name FROM myTable ORDER BY Name` order all rows, or even give error? I remember there was issues with this, at least ~8 years ago... — Andrei, Jun 15 '18 at 12:18
ORDER BY at the end is giving order (3, 0, 2, 1), i need order (0, 3, 2, 1) ... — Andrei, Jun 15 '18 at 13:53

score 0 · Answer 1 · answered Jun 15 '18 at 12:46

0

Or else you can move the order by outside:

SELECT 
    ID, Name
FROM
    (SELECT '' AS ID, '' AS Name UNION SELECT 
        ID, Name
    FROM
        yourTable) AS yourTableTmp
ORDER BY Name;

answered Jun 15 '18 at 12:46

vignz.pie

173
2
14

That's exactly what i am trying to avoid - i want `SELECT '' AS ID, '' AS Name` be out of `ORDER BY` and always be first row. – Andrei Jun 15 '18 at 12:51
Sorry if I'm understanding it wrong: You want **"", ""** to be on top of the HTML select, but you don't want to apply **ORDER BY** to **"", ""** with selected rows? Can you add more detail? – vignz.pie Jun 15 '18 at 12:58

Raymond Nijland · Answer 2 · 2018-06-15T14:38:49.040

0

You could use ORDER BY CASE ... END to change the defualt sorting behavior to get the expected result.

How it works

All '-' values get postion "1" within the ORDER BY
All NULL values get position "2" within the ORDER BY
All names get position "3+" and sorted on alphabet.

Query

SELECT '0' AS ID, '-' AS Name UNION 
SELECT ID, Name FROM (
  SELECT
     ID
   , Name
  FROM
   myTable
) AS myTableTmp
ORDER BY
 CASE 
  WHEN 
   Name = '-'
  THEN
   1
  WHEN 
   Name IS NULL
  THEN
   2
  ELSE 
   Name
 END

see demo https://www.db-fiddle.com/f/xcy45sUEDKN9gJhNpVqoHd/2

But what if someone put name '-' in table? i may get the wrong id in that case...

Query

SELECT
   '0' AS ID
 , '-' AS Name
 , 0 AS position

UNION

SELECT
   ID
 , Name
 , CASE
     WHEN 
      Name IS NULL
     THEN 
      2
     ELSE
      3
   END AS position
FROM (
   SELECT
       ID
     , Name
   FROM
    myTable 
) AS myTableTmp

ORDER BY 
   position
 , name ASC

see demo https://www.db-fiddle.com/f/xcy45sUEDKN9gJhNpVqoHd/7

edited Jun 15 '18 at 14:38

answered Jun 15 '18 at 13:54

Raymond Nijland

11,488
2
22
34

But what if someone put name '-' in table? i may get the wrong id in that case... – Andrei Jun 15 '18 at 14:00
1

"But what if someone put name '-' in table? i may get the wrong id in that case... " i've made a update @Andrei i think the update does a better job handling that case. – Raymond Nijland Jun 15 '18 at 14:16
Adding dedicated column for sorting may be one of right directions ... Problem is - my queries usually are big and ugly, if i also start adding CASE, CROSS JOIN and user variables to them, i i will not be able to read them myself :) – Andrei Jun 15 '18 at 14:31
Looking down on it you even don't need to use MySQL user variables for it to work. @Andrei.. The CASE is simply required or you need to break it down into a other UNION.something like this https://www.db-fiddle.com/f/xcy45sUEDKN9gJhNpVqoHd/8 – Raymond Nijland Jun 15 '18 at 14:44

score 0 · Accepted Answer · answered Jun 18 '18 at 06:06

I guess, most clean SQL only solution at the momet is as here Using union and order by clause in mysql

SELECT ID, Name FROM (
SELECT '0' AS ID, '-' AS Name, 1 AS Rank UNION 
SELECT ID, Name, 2 AS Rank FROM myTable
) AS myTableTmp ORDER BY Rank, Name

MySQL - ORDER BY with part of UNION

3 Answers3