So you want to:
- Find the first date after today
- Put this first in your results
- Sort everything else by date ascending
?
If so something like this can help:
CREATE TABLE t
(Order_id int, Quantity int, Schedule_Date date)
;
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
INSERT INTO t VALUES (1, 0, '2016-01-15 00:00:00');
INSERT INTO t VALUES (2, 0, '2016-11-20 00:00:00');
INSERT INTO t VALUES (3, 1, '2016-12-25 00:00:00');
select * from (
select row_number() over (order by case when schedule_date > sysdate then 1 else 2 end, schedule_date) rn,
t.*
from t
)
order by case when rn = 1 then 1 end, schedule_date;
RN ORDER_ID QUANTITY SCHEDULE_DATE
1 2 0 2016-11-20 00:00:00
3 1 0 2016-01-15 00:00:00
2 3 1 2016-12-25 00:00:00
The row_number sorts all the future dates first, then the past dates after these. The the order by takes the first of the future dates and maps everything else to null. So the first is top. Then finish by sorting everything else by date ascending.
Thanks to Martin Widlake for helping me work through the logic! :)