CODEWITHSUNDEEP
mysqlunion
Vinie
Vinie

Reputation: 2993

order by in only one query in UNION mysql

I have a table

Table User 

 Id      Name     status        
 1       Jhon     Approved        
 2       Endy     Decline
 3       Ally     In Review

I have to show a list of all user and sort only those rows which are not approved.

My code is below

(select name from user where status<>'Approved' order by status asc)
UNION  
(select name from user where status='Approved')

I am always getting this on both cases ASC and DESC

Id      Name     status             
 3      Ally     In Review
 2      Endy     Decline
 1      Jhon     Approved

Should same result for order by name also.

(select name from user where status<>'Approved' order by name asc)
UNION  
(select name from user where status='Approved')

 Id      Name     status             
 3       Ally     In Review
 2       Endy     Decline
 1       Jhon     Approved

Please help me. Your help would be appreciated.

Upvotes: 2

Views: 437

Answers (3)

Vinie
Vinie

Reputation: 2993

Thank you for your reply. I got the solution 

(select * from 
(select * from user where status <> 'Approved' order by status asc) a)
union 
(select * from t1 where status = 'Approved' )

Upvotes: 0

Nitin Tripathi
Nitin Tripathi

Reputation: 1254

Try changing union with union all, it works atleast in oracle

select * from 
(select * from t1 where status <> 'Approved' order by status asc)
union all
select * from 
(select * from t1 where status = 'Approved' )

output:

ID|NAME|STATUS
2|Endy|Declined
3|Ally|In Review
1|John|Approved

Hope it may help you. :)

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can use case when for the order by

select * from User 
order by  
case when status <> 'Approved' then 0 else 1 end ,status;

Upvotes: 1

Related Questions