Reputation: 3483
I am trying a different kind of sorting in Oracle.Its like I have three columns namely Date ,Start Time & End Time and I need to sort the column in the following fashion.
If the current time is in between Start Time and End time then that row should come @ the top.Otherwise it should be sorted by normal ascending order.
Right now my query looks like this
select * from details order by date,start_time
How can I take current time into consideration while sorting?
Upvotes: 0
Views: 655
Reputation: 1135
First idea that came to my mind:
SELECT *
FROM details
ORDER BY ( CASE WHEN date_column BETWEEN start_time AND end_time THEN 0 ELSE 1 END )
, date_column
, start_time;
Upvotes: 4
Reputation: 754690
You may need to tweak the syntax into Oracle, but the concept should apply.
SELECT d.*,
CASE WHEN sysdate BETWEEN Start_Time AND End_Time THEN 0 ELSE 1 END SortKey
FROM Details
ORDER BY SortKey, Date, Start_Time;
Basically, this creates a column that artificially groups the results as you want them sorted. It can be a valuable technique. I like to see the sort criteria - at least while debugging - so I put it in the SELECT-list. You can also keep it hidden in just the ORDER BY clause if you prefer.
Upvotes: 1
Reputation: 231781
If "current time" means "sysdate", and assuming that "sorted by normal ascending order" means "sort by start_time in ascending order" you could do something like
SELECT *
FROM details
ORDER BY (CASE WHEN sysdate BETWEEN start_time AND end_time
THEN 1
ELSE 0
END) desc,
start_time asc
If you mean something else, some sample data and the expected output would definitely help.
Upvotes: 4