Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, kishore.

Asked: September 19, 2016 - 8:51 pm UTC

Last updated: September 20, 2016 - 5:16 pm UTC

Version: 10G

Viewed 1000+ times

You Asked

Hi Tom,

I need to get the record of nearest future date as first record from the data set and rest by Ascending order
For example:

Before Sorting :
Order Quantity Schedule Date
1 0 Jan-15-2016
2 0 NOV-20-2016
3 1 DEC-25-2016

After Sorting :

Order Quantity Schedule Date
2 0 NOV-20-2016
1 0 Jan-15-2016
3 1 DEC-25-2016

and Chris said...

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! :)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank U

kishore, September 20, 2016 - 5:45 pm UTC

Hi Sir,

Thank you so much for quick response.


Regards,
K.Kishore

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.