CODEWITHSUNDEEP
sqloracle-databasesql-order-by
Pat
Pat

Reputation: 613

SQL order by using multiple columns using case

Table:

NUMBER DETAIL_ID,
Date schdeuledDate;
Date completedDate;
NUMBER recordId;

I am trying to implement this, retrieve records the most recent completed date and if null then most recent scheduled date and if more than one records retrieved then use higher recordId.

SELECT DETAIL_ID,completedDate,schdeuledDate,recordId FROM table
ORDER BY CASE completedDate WHEN NULL THEN schdeuledDate ELSE completedDate END,DETAIL_ID,recordId DESC;

But this what i get from the above query

Detail_ID  compdate     schdate    recordId
318        08-AUG-16    15-AUG-16   22342
318        06-JUN-18    22-AUG-18   26219
318        22-AUG-17    08-AUG-17   26218
418        null         08-AUG-17   26450
418        22-AUG-17    22-AUG-18   26879
418        22-AUG-17    22-AUG-18   26779

expected:

318        06-JUN-18    22-AUG-18   26219
418        22-AUG-17    22-AUG-18   26879

Upvotes: 1

Views: 152

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you want row_number():

select t.*
from (select t.*,
             row_number() over (partition by detail_id
                                order by coalesce(schdeuledDate, completedDate) desc, recordId desc
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions