SELECT
....
GROUP BY
c.city_id
ORDER BY p.name desc
UNION
SELECT
...
GROUP BY
c.city_id, p
ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY
is there a way to have this format because i want the top query to have an orderby do i need to have the same orderby on the bottom query
ORDER BY should be at the end of your select statement, not before the UNION.
The ORDER By comes at the end
select *
from..
union all
select *
from...
order by....
what you can do if you want the first query to show up first is this
select *, 1 as SortOrder
from..
union all
select * ,2 as SortOrder
from...
order by SortOrder,<other columns>...
You can't use an order by on the select queries that will be joined by the UNION. You can, if you want, select everything afterwards and add an order by then.
In standard SQL, the ORDER BY comes at the end of the UNION'd queries and is applied to the final result from those queries.
But...
MySQL allows you to use an ORDER BY within a UNION statement if you enclose it in brackets:
( SELECT ....
FROM ...
GROUP BY c.city_id
ORDER BY p.name DESC )
UNION
SELECT ...
FROM ...
GROUP BY c.city_id
...which'll also allow you to use LIMIT...
As the other answers say, It is being parsed as
SELECT { unordered_stuff UNION SELECT unordered_stuff } ORDER BY foo
Some, though I don't believe all, databases support an alternate disambigiouation syntax. This is from the Pg lists.
(SELECT * from foo where priority = 1 order by seniority)
UNION ALL
(select * from foo where priority > 1 order by seniority, priority)
Otherwise the ORDER BY is considered to apply to the whole UNION result
(it's effectively got lower binding priority than the UNION). Note also
that you *must* use UNION ALL, else UNION will attempt to eliminate
duplicates, and mess up the sort order while at it.
See also Bruno's solution nearby. Not sure which of these approaches
would be faster; try both.