MySQL - ORDER BY with part of UNION

mysql union order by not working
mysql union remove duplicates
mysql union all
mysql union distinct
mysql union vs union all
mysql union group by
incorrect usage of union and order by
mysql order by in union select

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?


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;

How to use union and order by clause in MySQL?, HIGH_PRIORITY cannot be used with SELECT statements that are part of a To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result,  Union is a type of operator in MySQL. We can use ORDER BY with this to filter records. Use UNION if you want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set.


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

MySQL 8.0 Reference Manual :: 13.2.10.3 UNION Clause, HIGH_PRIORITY cannot be used with SELECT statements that are part of a To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result,  MySQL UNION. UNION. In MySQL, the UNION operator is used to combine the result from multiple SELECT statements into a single result set. The default characteristic of UNION is, to remove the duplicate rows from the result. The DISTINCT keyword which is optional does not make any effect, because, by default, it specifies duplicate-row removal.


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 5.7 Reference Manual :: 13.2.9.3 UNION Clause, Just out of curiosity, can you try this version? It may trick the optimizer to use the same indices that the subqueries would use separately: SELECT * FROM  MySQL UNION and ORDER BY If you want to sort the result set of a union, you use an ORDER BY clause in the last SELECT statement as shown in the following example: SELECT concat (firstName, ' ',lastName) fullname FROM employees UNION SELECT concat (contactFirstName, ' ',contactLastName) FROM customers ORDER BY fullname;


MySQL: Optimize UNION with "ORDER BY" in inner queries , In MySQL the UNION is used to combine the result from multiple UNION SELECT employee_id,job_id FROM job_history ORDER BY  To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT : (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);


MySQL UNION, Ben Nadel demonstrates how to use the ORDER BY and LIMIT clauses in a SQL Using ORDER BY and LIMIT in a UNION query in MySQL. In MySQL 8.0.19 and later, UNION ALL and UNION DISTINCT work the same way when one or more TABLE statements are used in the union. To apply ORDER BY or LIMIT to an individual SELECT , place the clause inside the parentheses that enclose the SELECT :


Using ORDER BY And LIMIT Clauses In A UNION SQL Statement, This tutorial shows you how to use the MySQL UNION operator to combine If you want to sort the result set of a union, you use an ORDER BY clause in the last​  But, when I do, one of the features that makes it so powerful is the fact that you can apply both an ORDER BY clause and a LIMIT clause to the derived result set of the union (at least in MySQL). Furthermore, the individual SELECT statements can also have their own ORDER BY and LIMIT clauses. To see this in action, take a look at the following query.