Sort data with a particular text last in oracle

user_10199045 0
Viewed 30

I am trying to sort the below varchar field in oracle.

Source:

Not a Valid Version
Not a Valid Version
3.12p5s2

I will always have one value like '3.12p5s2' and other values with the text 'Not a Valid Version'. Text '3.12p5s2' can have different formats like '3.12s2' or '3.12d5'. I am trying to sort the data in such a way that 'Not a Valid Version' always comes last irrespective of the format in '3.12p5s2'.

Sample Output:

3.12p5s2
Not a Valid Version
Not a Valid Version

This is what I tried, this does give what I want but am not too sure if sorting ascending would work all the time.

select '3.12p5s2' as firmware from dual union all
select 'Not a Valid Version' as firmware from dual union all
select 'Not a Valid Version' as firmware from dual
order by firmware asc;

Any inputs will be valuable.

Thanks

sorting oracle
2 Answers

You can try something like this ( note "with" clause just to generate dummy data for example):

with test_table as (
select 'Not a Valid Version' c1 from dual 
union  select '3.12p5s2' c1 from  dual  )
select c1 , instr(c1,'Not') flag  from test_table
order by flag

INSTR function returns the index of a substring from the source, and if not found, returns 0. Since you don't show if there are other columns that need to be included in the sort, you will need to determine what a final query would look like, but hopefully, this gets you in the right direction.

user_17455440

If values are really as you said, then CASE might help (testing various versions):

SQL> with test (firmware) as
  2    (select 'Not a Valid Version' from dual union all
  3     select '&C3PO'               from dual union all
  4     select 'Not a Valid Version' from dual
  5    )
  6  select * From test
  7  order by case when firmware = 'Not a Valid Version' then 2
  8                else 1
  9           end;
Enter value for c3po: 3.12p52s

FIRMWARE
-------------------
3.12p52s
Not a Valid Version
Not a Valid Version

SQL> /
Enter value for c3po: 3.12s2

FIRMWARE
-------------------
3.12s2
Not a Valid Version
Not a Valid Version

SQL> /
Enter value for c3po: 3.12d5

FIRMWARE
-------------------
3.12d5
Not a Valid Version
Not a Valid Version

SQL>
user_90979060
Related