CODEWITHSUNDEEP
mysqlsqlsql-order-byunion
vidya sagar
vidya sagar

Reputation: 2009

SQL ORDER BY with UNION

I'm trying to create a view based on an existing table. The table will be like: 

+------+------+------+------+
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| 1    | a1   | a2   | a3   |
| 2    | b1   | b2   | b3   |
| 3    | c1   | c2   | c3   |
| 4    | d1   | d2   | d3   |
| 5    | e1   | e2   | e3   |
| 6    | f1   | f2   | f3   |
+------+------+------+------+

The resultant view should have rows in the following manner:

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| 1    | a1   | a3   |
| 2    | a2   | a3   |
| 3    | b1   | b3   |
| 4    | b2   | b3   |
| 5    | c1   | c3   |
| 6    | c2   | c3   |
+------+------+------+

My SQL query looks like this:

(select col1 as "col 1",col2 as "col 2",col4 as "col 3" from t1)
union
(select col1 as "col 1",col3 as "col 2",col4 as "col 3" from t1)
order by "col 2","col 3"

But the order by doesn't seem to work. I'm getting the same order of rows for whatever alias I give after the order by

Thanks in advance

Upvotes: 2

Views: 110

Answers (6)

Walter Mitty
Walter Mitty

Reputation: 18950

Your order by is specifying two constant character strings, rather than column names that contain a space. Try

select col1, col2, col4 as col3 from t1
union
select col1, col3 as col2, col4 as col3 from t1
order by col2, col3

This still isn't going to resequence col1 for you.

Upvotes: 0

Jens
Jens

Reputation: 69505

Try:

select * from (
  (select col1 as col1,col2 as col2,col4 as col3 from t1)
  union
  (select col1 as col1,col3 as col2,col4 as col3 from t1)
) as t
order by t.col2,t.col3

In your case only the second query will order the result.

Upvotes: 1

Arpit Khandelwal
Arpit Khandelwal

Reputation: 1803

SELECT AId AS A1, BId AS S2, CId  AS S3 FROM(
    SELECT AId, BId, CId FROM TableName
    UNION
    SELECT AId, BId, CId FROM TableName
) AS T ORDER BY S2, S3 DESC

But why would you need a union in two identical recordsets?

Upvotes: 0

Stuart
Stuart

Reputation: 699

This should do it:

SELECT COL_1, COL_2, COL_3 
FROM ((SELECT COL1 AS COL_1, COL2 AS COL_2, COL4 AS COL_3 FROM T1) 
UNION (SELECT COL1 AS COL_1, COL3 AS COL_2 , COL4 AS COL_3 FROM T1)) 
ORDER BY COL_1, COL_2

Or simply like this:

(SELECT COL1 AS COL_1, COL2 AS COL_2, COL4 AS COL_3 FROM T1) 
UNION 
(SELECT COL1 AS COL_1, COL3 AS COL_2 , COL4 AS COL_3 FROM T1)
ORDER BY COL_1, COL_2

Upvotes: 0

Andriy M
Andriy M

Reputation: 77737

It is very likely that your MySQL instance is configured to treat double-quoted strings as string literals rather than quoted identifiers. If you use MySQL's native backticks to delimite the names in ORDER BY, you will get the expected result:

(select col1 as "col 1",col2 as "col 2",col4 as "col 3" from t1)
union
(select col1 as "col 1",col3 as "col 2",col4 as "col 3" from t1)
order by `col 2`,`col 3`

Upvotes: 2

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

Try this 

(select col1 as c1,col2 as c2,col4 as c3 from t)
union
(select col1 as c1,col3 as c2,col4 as c3 from t)
order by c2,c3

Demo

Upvotes: 0

Related Questions