Using different order by with union
This should work:
SELECT *
FROM (SELECT TOP 10 A.*, 0 AS Ordinal
FROM A
ORDER BY [Price]) AS A1
UNION ALL
SELECT *
FROM (SELECT TOP 3 A.*, 1 AS Ordinal
FROM A
ORDER BY [Name]) AS A2
ORDER BY Ordinal
From MSDN:
In a query that uses UNION, EXCEPT, or INTERSECT operators, ORDER BY
is allowed only at the end of the statement. This restriction applies
only to when you specify UNION, EXCEPT and INTERSECT in a top-level
query and not in a subquery.
Edited: to force the order you need to apply an ORDER BY
to the outer query. I've added a constant value column to both queries.
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
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
Using group/order by with union clause in sql query
A union
query may only have one order by
clause.
If you are satisfied with ordering the whole resultset, you can remove all order by
clauses and just keep the very last one, at the end of the query. It applies to the entire dataset that union
generates.
Note that your UNION
s are equivalent to UNION ALL
s - because the client name is different in each member - and should be phrased as such.
If, on the other hand, you want to order reach sub-result, then this is different. Basically you need a flag in each member, that can then be used to identify each group. The client name might be a good pick, so:
order by client_name, client_id
How to use order by with union all in sql?
SELECT *
FROM
(
SELECT * FROM TABLE_A
UNION ALL
SELECT * FROM TABLE_B
) dum
-- ORDER BY .....
but if you want to have all records from Table_A
on the top of the result list, the you can add user define value which you can use for ordering,
SELECT *
FROM
(
SELECT *, 1 sortby FROM TABLE_A
UNION ALL
SELECT *, 2 sortby FROM TABLE_B
) dum
ORDER BY sortby
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
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
Create a UNION ALL query when the columns are in different order
Alas, no. UNION ALL
goes by position not by names. However, you can generate the columns:
select string_agg(column_name, ', ')
from information_schema.columns
where table_name = ? and
table_schema = ?;
You can then plug the list into your code.
Related Topics
Tree Structure in SQL in Oracle.How to Show Tree,Child Nodes and Parent Nodes in SQL Oracle
Swap Values for Two Rows in the Same Table in SQL Server
Do You Prefer Verbose Naming When It Comes to Database Columns
Using Variables for Asc and Desc in Order By
Creating Sumif Function in SQL Server 2012
What Is the Equivalent of Xml Path and Stuff in Linq Lambda Expression (Group_Concat/String_Agg)
How to Return Only Work Time from Reservations in Postgresql
See SQL from Entityframework with Collection-Like Queries
Postgres SQL 'Could Not Determine Data Type of Parameter' by Hibernate
Left Inner Join VS. Left Outer Join - Why Does the Outer Take Longer
How to Subtract Two Row's Values Within Same Column Using SQL Query
SQL Function to Get Count of How Many Times String Appears in Column
Deleting a Row with a Self-Referencing Foreign Key
How to Calculate Running Multiplication
How to Insert Values into the Database Table Using Vba in Ms Access