The order by case when problems-CodePudding

Want to achieve at present, the data before today, l arrange sorting, data after today, descending order

 select * from (
SELECT 
A.r eserve_id, 
A.s tart_date_time, 
Arjun eeting_name, 
B.a ddress. 
A.c ontact, 
A.s ponsor, 
A.o ffice_number, 
Amy polumbo hone_number, 
A. ` status ` 
The FROM 
Meeting_reserve a 
LEFT the JOIN meeting_room b ON a.r oom_id=b.r oom_id 
The ORDER BY a.s tart_date_time desc 
) as x 
The order by 
Case the when date_format (x.s tart_date_time, 'Y - m - % d % %') & lt; Date_format (date (' 2017-08-11 '), 'Y - m - % d % %) then x.s tart_date_time end desc, 
Case the when date_format (x.s tart_date_time, 'Y - m - % d % %') & gt;=date_format (date (' 2017-08-11 '), 'Y - m - % d % %) then x.s tart_date_time end

But found that after today's data, all in the end,

The 2017-08-10 12:00:00 learning conference
The 2017-08-10 09:00:00 flood control conference
The 2017-08-10 06:00:00 peacekeeping meeting
2017-08-09 00:00:00 CPPCC
2017-08-09 00:00:00 test meeting
The 2017-08-11 06:00:00 chat
The 2017-08-11 07:00:00 prevent waterlogging meeting
The 2017-08-11 12:00:00 teaching conference

Hope to be able to put the data after today, up front, don't know if I can achieve

CodePudding user response:

Of course, there is another way,

Use the union, but the use of the union, each of the order by the needs and limit are used together,
Now don't want to use the limit to display search article number, (I know you can set the limit to the bigger,,)

Just want to know, if you don't use the union, can achieve

CodePudding user response:

 select * from (
SELECT 
A.r eserve_id, 
A.s tart_date_time, 
Arjun eeting_name, 
B.a ddress. 
A.c ontact, 
A.s ponsor, 
A.o ffice_number, 
Amy polumbo hone_number, 
A. ` status ` 
The FROM 
Meeting_reserve a 
LEFT the JOIN meeting_room b ON a.r oom_id=b.r oom_id 
The ORDER BY a.s tart_date_time desc 
) as x 
The order by 
Start_date_time desc, 
The case when x.s tart_date_time & gt; Then='2017-08-11' x.s tart_date_time end, 
The case when x.s tart_date_time & lt; '2017-08-11' then x.s tart_date_time end desc

CodePudding user response:

refer to the second floor hongmei85 response:

 select * from (
SELECT 
A.r eserve_id, 
A.s tart_date_time, 
Arjun eeting_name, 
B.a ddress. 
A.c ontact, 
A.s ponsor, 
A.o ffice_number, 
Amy polumbo hone_number, 
A. ` status ` 
The FROM 
Meeting_reserve a 
LEFT the JOIN meeting_room b ON a.r oom_id=b.r oom_id 
The ORDER BY a.s tart_date_time desc 
) as x 
The order by 
Start_date_time desc, 
The case when x.s tart_date_time & gt; Then='2017-08-11' x.s tart_date_time end, 
The case when x.s tart_date_time & lt; '2017-08-11' then x.s tart_date_time end desc

Thank you for your reply
You this way I tried before,
Retrieved is such,
The 2017-08-11 12:00:00 teaching conference
The 2017-08-11 07:00:00 prevent waterlogging meeting
The 2017-08-11 06:00:00 chat
The 2017-08-10 12:00:00 learning conference
The 2017-08-10 09:00:00 flood control conference
The 2017-08-10 06:00:00 peacekeeping meeting
2017-08-09 00:00:00 CPPCC
2017-08-09 00:00:00 test meeting

CodePudding user response:

To solve the

 
SELECT 
A.r eserve_id, 
A.s tart_date_time, 
Arjun eeting_name, 
B.a ddress. 
A.c ontact, 
A.s ponsor, 
A.o ffice_number, 
Amy polumbo hone_number, 
A. ` status ` 
The FROM 
Meeting_reserve a 
LEFT the JOIN meeting_room b ON a.r oom_id=b.r oom_id 
The order by 
A.s tart_date_time & lt; '2017-08-11', the if (a.s tart_date_time & lt; '2017-08-11', 0, a.s tart_date_time), a.s tart_date_time desc

CodePudding user response: