Harish
Harish

Reputation: 3483

Sorting a column in Oracle based on time

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

Answers (3)

Szilard Barany
Szilard Barany

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

Jonathan Leffler
Jonathan Leffler

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

Justin Cave
Justin Cave

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

Related Questions