CODEWITHSUNDEEP
mysqlsql
Amandeep Singh
Amandeep Singh

Reputation: 3840

using order by with union in inner select queries

I want to use order by clause in inner select queries with union operator i.e.

select colname from table where <cond1> order by colname 
union
select colname from table where <cond2> order by colname

I can use order by with the complete result but I want to have the results from first query ordered by and then the result of second query ordered by.

Please give me some suggestions upon it.

Upvotes: 0

Views: 584

Answers (3)

John Woo
John Woo

Reputation: 263803

the correct usage of Order by in unioned table is

select colname from table where <cond1> 
union
select colname from table where <cond2> order by colname

the explanation of the code above is, after the the two queries had been unioned, it then Sorts based on the column name. It doesn't sort the lower query first before the server union it with the first query. But there is an alternative for that, you need to wrap it in a subquery.

SELECT newTable.colname
FROM
(
    select colname, 1 as OrderThis from table where <cond1> 
    union
    select colname, 2 as OrderThis from table where <cond2> 
) newTable
Order by newTable.OrderThis, newTable.colname

if i got your idea correctly, you want to sort the columns first before you union the two queries right and maintaining their correct position (first query results remains on top while the second query results remain below the first query)

Upvotes: 1

Captain Jack Sparrow
Captain Jack Sparrow

Reputation: 449

Following should do... i guess 

select colname,1 as OrderCol from table where <cond1> 
union select colname,2 as OrderCol  from table 
where <cond2>  
order by OrderCol

Upvotes: 1

Zain Ali
Zain Ali

Reputation: 15983

This may do the trick

SELECT * FROM ( SELECT colname  FROM table1 where <cond1> order by colname ) DUMMY_ALIAS1

UNION ALL

SELECT * FROM ( SELECT colname  FROM table2 where <cond2> order by colname ) DUMMY_ALIAS2

Upvotes: 0

Related Questions