MySQL - ORDER BY with part of UNION
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?
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
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.
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
But what if someone put name '-' in table? i may get the wrong id in that case...
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
MySQL 8.0 Reference Manual :: 220.127.116.11 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 :: 18.104.22.168 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.