Using union and order by clause in mysql
You can do this by adding a pseudo-column named rank to each select, that you can sort by first, before sorting by your other criteria, e.g.:
select *
from (
select 1 as Rank, id, add_date from Table
union all
select 2 as Rank, id, add_date from Table where distance < 5
union all
select 3 as Rank, id, add_date from Table where distance between 5 and 15
) a
order by rank, id, add_date desc
Apply ORDER BY on a UNION (Mysql)
SELECT *
FROM (
(SELECT * FROM user_relation WHERE from_user_id = 1)
UNION
(SELECT * FROM user_relation WHERE to_user_id = 1)
) AS i
ORDER BY trust_degree
You have to assign an alias to your select. But in this case a UNION
is not necessary and could be replaced by a simple OR
, as @Karoly Horvath points out in his comment. The resulting query would look like this:
SELECT
*
FROM user_relation
WHERE from_user_id = 1 OR to_user_id = 1
ORDER BY trust_degree
Error with ORDER BY clause using UNION in MySQL
The results of your UNION
do not include any fields from table 'u', so those results cannot be sorted by table 'u' fields.
You could perhaps perform the UNION and then re-join the results to table 'u', and then use that to sort the results by table 'u' fields. A similar issue exists for sorting on course.fullname
, so that would need to be joined back in, too.
SELECT x.id, x.userid, x.status, x.timestart, x.timeend, x.courseid, x.enrolid, x.roleid
FROM ((SELECT ue.id, ue.userid, ue.status, ue.timestart, ue.timeend, e.courseid,
e.id AS enrolid, ra.roleid
FROM user_enrolments ue
JOIN enrol e ON e.id = ue.enrolid
JOIN course c ON c.id = e.courseid
JOIN user u ON u.id = ue.userid
JOIN context ct ON ct.instanceid = c.id
LEFT JOIN role_assignments ra ON ra.userid = u.id
AND ra.contextid = ct.id
AND ra.itemid = e.id
WHERE e.customint1 = 1 AND u.deleted = 0
AND ct.contextlevel = 50 AND (ue.status = 0 OR ue.status = 1))
UNION
(SELECT de.enrolid AS id, de.userid, de.status, de.date_ini, de.date_fin,
de.courseid, de.enrolid, de.roleid
FROM deleted_enrols de
JOIN user u ON u.id = de.userid
WHERE userid = ANY (SELECT userid FROM local_users WHERE clientid = 1))
) x
JOIN user z ON z.id = x.userid
JOIN course d ON d.id = x.courseid
ORDER BY z.firstname, z.lastname, d.fullname LIMIT 0, 100
How to use ORDER BY inside UNION
Something like this should work in MySQL:
SELECT a.*
FROM (
SELECT ... FROM ... ORDER BY ...
) a
UNION ALL
SELECT b.*
FROM (
SELECT ... FROM ... ORDER BY ...
) b
to return rows in an order we'd like them returned. i.e. MySQL seems to honor the ORDER BY
clauses inside the inline views.
But, without an ORDER BY
clause on the outermost query, the order that the rows are returned is not guaranteed.
If we need the rows returned in a particular sequence, we can include an ORDER BY
on the outermost query. In a lot of use cases, we can just use an ORDER BY
on the outermost query to satisfy the results.
But 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. For example, add ,'a' AS src
in the first query, ,'b' AS src
to the second query.
Then the outermost query could include ORDER BY src, name
, to guarantee the sequence of the results.
FOLLOWUP
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.
The "trick" in query in my answer (above) is dependent on behavior that may be specific to some versions of MySQL.
Test case:
populate tables
CREATE TABLE foo2 (id INT PRIMARY KEY, role VARCHAR(20)) ENGINE=InnoDB;
CREATE TABLE foo3 (id INT PRIMARY KEY, role VARCHAR(20)) ENGINE=InnoDB;
INSERT INTO foo2 (id, role) VALUES
(1,'sam'),(2,'frodo'),(3,'aragorn'),(4,'pippin'),(5,'gandalf');
INSERT INTO foo3 (id, role) VALUES
(1,'gimli'),(2,'boromir'),(3,'elron'),(4,'merry'),(5,'legolas');
query
SELECT a.*
FROM ( SELECT s.id, s.role
FROM foo2 s
ORDER BY s.role
) a
UNION ALL
SELECT b.*
FROM ( SELECT t.id, t.role
FROM foo3 t
ORDER BY t.role
) b
resultset returned
id role
------ ---------
3 aragorn
2 frodo
5 gandalf
4 pippin
1 sam
2 boromir
3 elron
1 gimli
5 legolas
4 merry
The rows from foo2
are returned "in order", followed by the rows from foo3
, again, "in order".
Note (again) that this behavior is NOT guaranteed. (The behavior we observer is a side effect of how MySQL processes inline views (derived tables). This behavior may be different in versions after 5.5.)
If you need the rows returned in a particular order, then specify an ORDER BY
clause for the outermost query. And that ordering will apply to the entire resultset.
As 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. I would also do away with the inline views, and do something like this:
SELECT s.id, s.role, 's' AS src
FROM foo2 s
UNION ALL
SELECT t.id, t.role, 't' AS src
FROM foo3 t
ORDER BY src, role
Mysql multiple ORDER BY with UNION
Try this
SELECT n.nid, max(na.gid) as mid,fav.field_date_posted_value, UNIX_TIMESTAMP(fav.field_date_posted_value) as pdate, 1 as ob FROM `node` as n
JOIN nodeaccess AS na ON na.nid = n.nid
LEFT JOIN field_data_field_date_posted AS fav ON fav.entity_id = n.nid
WHERE (na.gid IN(10,11) AND (n.status = '1') AND (n.type IN ('article','blog', 'events', 'media', 'press_releases', 'expert_speak', 'feature', 'case_study', 'news', 'the_igtb_series', 'trend', 'white_paper')) )
GROUP BY n.nid
UNION ALL
SELECT n.nid, max(na.gid) as mid,fav.field_date_posted_value, UNIX_TIMESTAMP(fav.field_date_posted_value) as pdate, 2 as ob FROM `node` as n
JOIN nodeaccess AS na ON na.nid = n.nid
LEFT JOIN field_data_field_date_posted AS fav ON fav.entity_id = n.nid
WHERE (na.gid IN(2) AND (n.status = '1') AND (n.type IN ('article','blog', 'events', 'media', 'press_releases', 'expert_speak', 'feature', 'case_study', 'news', 'the_igtb_series', 'trend', 'white_paper')) )
GROUP BY n.nid
ORDER BY ob ASC, pdate DESC
Fixed the order by date only base or group
UNION ALL - ORDER BY not working as expected ...?
Manually add a sort ordering for the selected item:
SELECT
animation_id,
animation_name,
animation_group,
CASE WHEN animation_id = '45' THEN 1 ELSE 0 END AS is_selected
FROM
animations
WHERE
animation_id = '45' OR (animation_id <> '45' AND active = true)
ORDER BY
is_selected DESC,
animation_group,
animation_name
If you still want to keep your UNION
for some reason:
SELECT
animation_id,
animation_name,
animation_group,
1 AS is_selected
FROM
animations
WHERE
animation_id = '45'
UNION ALL -- If animation_id is unique, I can't see why you'd need a UNION ALL here, by the way
SELECT
animation_id,
animation_name,
animation_group,
0 AS is_selected
FROM
animations
WHERE
animation_id <> '45' AND
active = true
ORDER BY
is_selected DESC,
animation_group,
animation_name
Also, assuming animation_id
is a unique integer, 1) you don't need UNION ALL, as each row selected will be unique anyway, and 2) you don't need the quotes around the values, i.e. just animation_id = 45
will work, rather than animation_id = '45'
.
Sort order of each select statement in a mysql union
Try this:
Select id,name from
(
Select * from (
SELECT
t.id,
t.name,
@rownum := @rownum + 1 as row_number
FROM
test t
cross join (select @rownum := 0) r
WHERE
t.active=1
order by t.name ) tbl1
union all
select * from
(
SELECT
t.id,
t.name,
@rownum := @rownum + 1 as row_number
FROM
test2 t
cross join (select @rownum := 0) r
WHERE
t.active=1
order by t.id
) tbl2
) x order by row_number
you should use union all
https://www.db-fiddle.com/f/dzBHKuzGmaQC8EmyuWcH2Y/2
mysql union changing order
Use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows.1
It then gives this example of how to order the union result:
(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;
In your query you probably want to remove the order by
in the individual sub-queries and add this after:
ORDER BY grade DESC, name, marks
Related Topics
Querying Data by Joining Two Tables in Two Database on Different Servers
SQL - Many-To-Many Table Primary Key
Postgresql: Which Datatype Should Be Used for Currency
Left Outer Join Using + Sign in Oracle 11G
SQL Server 2008: How to Grant Privileges to a Username
How to Insert Datetime Value into a SQLite Database
What Does Include() Do in Linq
MySQL "Between" Clause Not Inclusive
How to Use Any Instead of in in a Where Clause
Insert into Multiple Tables in One Query
SQL Server 2005 Pivot on Unknown Number of Columns
Does Count(*) Always Return a Result
How to Split a Single Column Values to Multiple Column Values
Why Is SQL Server Losing a Millisecond
Different Current_Timestamp and Sysdate in Oracle
SQL Server: Howto Get Foreign Key Reference from Information_Schema