Vahe Shak Vahe Shak - 1 month ago 4
MySQL Question

How to union two ordered queries in the same table

I have events table with columns
'title',
'description',
'start_date',
'end_date'.

I want to get ordered list live and future events, depends on 'start_date' and 'end_date'.

I try

(
select *
from `events`
where `start_date` < NOW() and `end_date` > NOW()
order by `start_date` desc
)
union all
(
select *
from `events`
where `start_date` > NOW()
order by `start_date` desc)


but result have not that ordering which I want. I want at first ordered list by start_date live events after that ordered list by start_date future events.

Answer

Try this:

select * from (
(
    select start_date, end_date, 'Live' as event_type
    from `events`
    where `start_date` < NOW() and `end_date` > NOW() 
) 
union
(
    select start_date, end_date, 'Future' as event_type
    from `events`
    where `start_date` > NOW() ) ) a
ORDER BY event_type desc
, case when event_type = 'Live' then start_date end desc
, case when event_type = 'Future' then start_date end asc;