CODEWITHSUNDEEP
mysqlsql-order-byunion
Ram Singh
Ram Singh

Reputation: 6918

Order by is not working correctly with Union ALL my sql

I have query where i want to list result as below:

1
      2

I have used below query to achiece it, but it is not working as expected, and sorting the result ASC or DESC.

SELECT * from (SELECT eventid, eventdate,eventdates FROM events WHERE events.onlineclosingdate>curdate() order by onlineclosingdate asc) a
UNION  ALL
SELECT * from (SELECT eventid, eventdate,eventdates FROM events WHERE events.onlineclosingdate<=curdate() order by onlineclosingdate desc) as c

Upvotes: 0

Views: 707

Answers (2)

Ramgy Borja
Ramgy Borja

Reputation: 2458

Union two queries and group in One

SELECT * FROM (
SELECT a.eventid as `eventid`, a.eventdate as `eventdate`, a.eventdates as `eventdates`  FROM events a WHERE a.onlineclosingdate>curdate() order by a.onlineclosingdate asc)
UNION  ALL
SELECT c.eventid as `eventid`, c.eventdate as `eventdate`, c.eventdates as `eventdates` FROM events c WHERE c.onlineclosingdate<=curdate() order by c.onlineclosingdate desc)
) as `all`

Getting result like this

Upvotes: -1

P.Salmon
P.Salmon

Reputation: 17615

Perhaps you need to identify the 2 date period blocks and use a conditional order by for example Given

drop table if exists t;
create table t (id int auto_increment primary key, dt date);

insert into t (dt) values
('2017-02-01'),('2017-10-01'),('2017-01-01'),
('2016-02-01'),('2016-10-01'),('2016-01-01');

select s.id,s.dt 
from
(
select 1 as srce,id,t.dt dt from t where dt > str_to_date('2016-12-31','%Y-%m-%d') 
union all
select 2,id,t.dt from t where dt <= str_to_date('2016-12-31','%Y-%m-%d') 
) s
order by srce asc,
            case when s.srce = 1 then s.dt end asc, 
            case when s.srce = 2 then s.dt end desc;

+----+------------+
| id | dt         |
+----+------------+
|  3 | 2017-01-01 |
|  1 | 2017-02-01 |
|  2 | 2017-10-01 |
|  5 | 2016-10-01 |
|  4 | 2016-02-01 |
|  6 | 2016-01-01 |
+----+------------+
6 rows in set (0.00 sec)

Upvotes: 2

Related Questions