CODEWITHSUNDEEP
sqloracle-databasesql-order-by
jjones150
jjones150

Reputation: 302

I need to do a Custom Oracle Sort

I'm sorry if this is a simple question. However, I have searched high and low for the answer over about an hour.

I have a db_table with that I need to sort for output to a table in a webpage. The data is stored in the db_table in the following way:

   date          Area         Value
   ------        ------      -------
 11-mar-18        middle        10
 11-mar-18        bottom         5
 11-mar-18        top           12

 12-mar-18        top           14
 12-mar-18        bottom         4
 12-mar-18        middle        17

The question is: how can I sort these to produce the following result:

    date          Area         Value
   ------        ------      -------
 11-mar-18        top           12 
 11-mar-18        middle        10
 11-mar-18        bottom         5 

 12-mar-18        top           14
 12-mar-18        middle        17
 12-mar-18        bottom         4

Any help is greatly appreciated.

Upvotes: 3

Views: 3905

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Sort area descending alphabetically as the second component of order by :

select *
  from db_table
 order by "date", area desc;

As you mentioned if area has values A,B,C,D and they wanted to be sorted in the order of C,B,D,A, then use :

select *
  from db_table
 order by "date", decode(area,'C',1,'B',2,'D',3,'A',4);

P.S. especially, i put date column inside quotes, since already created table with "date" column, instead of date which is impossible as being a keyword.

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269533

instr() provides a shorter solution than case in most cases:

select t.*
from t
order by t.date, instr('top,middle,bottom', area)

Upvotes: 4

kc2018
kc2018

Reputation: 1460

select *
  from db_table
 order by "date", 
    case 
       when area = 'top' then 1 
       when area = 'middle' then 2
       when area = 'bottom' then 3
       else 4
    end;

Refer to Custom Sort Order

Upvotes: 4

Related Questions