![]() ![]() LIMIT, so that it is used to determine the subset of the selected rows The use of ORDER BY in this context is typically in conjunction with Nothing about the order in which the rows appear in the final resultīecause UNION by default produces an unordered set of rows. However, use of ORDER BY for individual SELECT statements implies (SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10) Inside the parentheses that enclose the SELECT: (SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION To apply ORDER BY or LIMIT to an individual SELECT, place the clause The MySQL docs on UNION explain why (emphasis mine): I would also do away with the inline views, and do something like this: SELECT s.id, s.role, 's' AS srcĭon't use ORDER BY in an individual SELECT statement inside a UNION, unless you're using LIMIT with it. And that ordering will apply to the entire resultset.Īs I mentioned earlier, if I needed the rows from the first query first, followed by the second query, I would include a "discriminator" column in each query, and then include the "discriminator" column in the ORDER BY clause. If you need the rows returned in a particular order, then specify an ORDER BY clause for the outermost query. This behavior may be different in versions after 5.5.) (The behavior we observer is a side effect of how MySQL processes inline views (derived tables). Note (again) that this behavior is NOT guaranteed. The rows from foo2 are returned "in order", followed by the rows from foo3, again, "in order". Populate tables CREATE TABLE foo2 (id INT PRIMARY KEY, role VARCHAR(20)) ENGINE=InnoDB ĬREATE TABLE foo3 (id INT PRIMARY KEY, role VARCHAR(20)) ENGINE=InnoDB The "trick" in query in my answer (above) is dependent on behavior that may be specific to some versions of MySQL. In your original query, the ORDER BY in your queries is discarded by the optimizer since there is no ORDER BY applied to the outer query, MySQL is free to return the rows in whatever order it wants. ![]() ![]() Then the outermost query could include ORDER BY src, name, to guarantee the sequence of the results. For example, add ,'a' AS src in the first query, ,'b' AS src to the second query. In a lot of use cases, we can just use an ORDER BY on the outermost query to satisfy the results.īut when we have a use case where we need all the rows from the first query returned before all the rows from the second query, one option is to include an extra discriminator column in each of the queries. If we need the rows returned in a particular sequence, we can include an ORDER BY on the outermost query. MySQL seems to honor the ORDER BY clauses inside the inline views.īut, without an ORDER BY clause on the outermost query, the order that the rows are returned is not guaranteed. To return rows in an order we'd like them returned. Something like this should work in MySQL: SELECT a.* ![]()
0 Comments
Leave a Reply. |