Oracle SQL Custom sorting

Question

Here I want to get sorted rows as 3,4,5,1,2,6,7,8,9 based on status or based on its description,

Status Description
1   New
2   Hold
3   Counter-Proposed
4   Partial-Counter-Proposed
5   Confirmed
6   Partial response Accept
7   Respone Accept
8   Response Reject
9   Cancelled

I tried with union all by selection only 3,4,5 as a set and 1,2,6,7,8,9 as another set is there any other easyway instead of multiple sets.Please guide to get solution.

WW. · Accepted Answer · 2013-12-11 06:45:45Z

3

@RedFilters answer works.

However, you could argue that there is a missing column on Status. You should consider adding a column to Status to represent which "set" the status is in. Then you can join from your main table to Status, and sort by this new column.

This way, you would not have to remember to update any code if you add an additional status.

answered Dec 11, 2013 at 6:45

WW.

24.4k15 gold badges99 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

D'Arcy Rittich · Accepted Answer · 2013-12-11 06:42:59Z

2

select Status, Description
from Status
order by case when Status in (3,4,5) then 1 else 2 end, Status

answered Dec 11, 2013 at 6:42

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

3 Comments

Jericho Over a year ago

Thank you it really helpful ! Could you redirect me to read something with this syntax " order by case when Status in (3,4,5) then 1 else 2 end, Status " ,because i didn't understand the final status why it comes.

tvm Over a year ago

First, all entries with Status in 3,4,5 get 1, thus they sort first, rest of the rows gets 2, so they sort last. Then they get additionally sorted by their real status. So, the returning order is (1,3),(1,4),(1,5),(2,1),(2,2),(2,6) and so on.

Jericho Over a year ago

Thanks for reply, so like temp memory they update the status value while this order by and shows the sorted and original values am I right?

DaveG · Accepted Answer · 2015-08-12 13:53:27Z

1

You could do the following, for any custom order:

SELECT Status, Description FROM yourtable ORDER BY FIND_IN_SET(Status,'3,4,5,1,2,6,7,8,9');

Take care: if the Status is not in the list, it will return 0 and therefore will be placed before everything else. If this is not wanted, you could do the following, where every occurence of 0 is replaced by a very large number:

SELECT
  Status, Description
FROM 
  yourtable 
ORDER BY 
    CAST(REPLACE(FIND_IN_SET(Status,'3,4,5,1,2,6,7,8,9'),0,4294967295) AS UNSIGNED) ASC;

edited Aug 12, 2015 at 13:53

answered Aug 12, 2015 at 9:33

DaveG

7536 silver badges16 bronze badges

Collectives™ on Stack Overflow

Oracle SQL Custom sorting

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related