Home » SQL & PL/SQL » SQL & PL/SQL » Sorting logic not working for all data conditions (10g)
Sorting logic not working for all data conditions [message #360061] |
Wed, 19 November 2008 07:12  |
J1357
Messages: 33 Registered: November 2008
|
Member |
|
|
Forum,
Requirement is to sort the data in
a)Numerically descending which will be shown in group first
b)then sort on alphabetically ascending which will be shown in next group.
The data consists of characters as well as numerics which may occur at any instance.
Sample Data :
ABC TelePresence Manager Release 1.3
ABC CRS-1 16-Slot Line Card Chassis
ABC XR 12404 Router
ABC XR 12000 Series Router
ABC TelePresence Manager Release 1.2
500 Series Router
ABC CRS-1 8-Slot Line-Card Chassis
ABC TelePresence Manager Release 1.1
12000 Series Router
ABC CRS-1 Line Card Chassis (Multi)
ABC CRS-1 Fabric-Card Chassis
450 Series Router
490 Series Router
Sample Output :
12000 Series Router
500 Series Router
490 Series Router
450 Series Router
ABC CRS-1 16-Slot Line Card Chassis
ABC CRS-1 8-Slot Line-Card Chassis
ABC CRS-1 Fabric-Card Chassis
ABC CRS-1 Line Card Chassis (Multi)
ABC TelePresence Manager Release ()
ABC TelePresence Manager Release 1.3
ABC TelePresence Manager Release 1.2
ABC TelePresence Manager Release 1.1
ABC XR <XXXX> Router ()
ABC XR 12404 Router
ABC XR 12000 Series Router
See the first set is sorted in Numeric descending logic
Then the next is sorted in alphabetically ascending+numeric descending logic
then next one is sorted in alphabetically ascending logic
like wise.
The combination of (Numeric Descending + Alphabet Ascending) needs to be applied over here.
One more thing is that the above logic build builds 1 more tree stuff like which is highlighted with ()
The query should be good enough to handle special characters wuch as "-,.()" which also should be sorted as per the rules.
The query which i have written does not seem to post the same logic but i need you are advice to sort on given logic :
Query:
select txt
from (select txt
,to_number(trim(regexp_substr(txt,'(^| )[0-9]*'))) as num1
,regexp_substr(txt,'(^| )[A-Z]*') as alpha1
,to_number(trim(regexp_substr(txt,'(^| )[0-9]*',1,2))) as num2
,regexp_substr(txt,'(^| )[A-Z]*',1,2) as alpha2
from t) t
order by t.num1 desc nulls last
, t.alpha1 nulls last
, t.num2 desc nulls last
, t.alpha2 nulls last
Help Appreciated.
|
|
|
|
Re: Sorting logic not working for all data conditions [message #360069 is a reply to message #360065] |
Wed, 19 November 2008 07:49   |
J1357
Messages: 33 Registered: November 2008
|
Member |
|
|
I need either a procedure or function to sort the string in
1) Numerically which will be descending
2) Sort the next set of data in Alphabet asceding with foll
a) Numeric data s/d be sorted descending
b) Alphabets s/d be sorted asceding.
Hope I've not missed any thing.
|
|
|
|
|
|
Re: Sorting logic not working for all data conditions [message #360102 is a reply to message #360075] |
Wed, 19 November 2008 11:00   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
He posted enough test data for me.
Test Case:create table test_022 (col_1 varchar2(100));
insert into test_022 values ('ABC TelePresence Manager Release 1.3');
insert into test_022 values ('ABC CRS-1 16-Slot Line Card Chassis');
insert into test_022 values ('ABC XR 12404 Router');
insert into test_022 values ('ABC XR 12000 Series Router');
insert into test_022 values ('ABC TelePresence Manager Release 1.2');
insert into test_022 values ('500 Series Router');
insert into test_022 values ('ABC CRS-1 8-Slot Line-Card Chassis');
insert into test_022 values ('ABC TelePresence Manager Release 1.1');
insert into test_022 values ('12000 Series Router');
insert into test_022 values ('ABC CRS-1 Line Card Chassis (Multi)');
insert into test_022 values ('ABC CRS-1 Fabric-Card Chassis');
insert into test_022 values ('450 Series Router');
insert into test_022 values ('490 Series Router');
Solution 1: Make the two alpha columns select everything that is space delimited, sort by numbers first, and then by the trailing version number:
select col_1, num1,alpha1,num2,alpha2,trail
from (select col_1
,to_number(trim(regexp_substr(col_1,'(^| )[0-9]*'))) as num1
,regexp_substr(col_1,'(^| )[^ ]*') as alpha1
,to_number(trim(regexp_substr(col_1,'(^| )[0-9]*',1,2))) as num2
,regexp_substr(col_1,'(^| )[^ ]*',1,2) as alpha2
,regexp_substr(col_1,' [0-9.]+$') as trail
from test_022) t
order by t.num1 desc nulls last
, t.alpha1 nulls last
, t.num2 desc nulls last
, t.alpha2 nulls last
, t.trail desc nulls last;
Solution 2:
Exploit the fact that numbers come first in the ascii table, so sort the dump of the strings (with additional sort by trailing version)
select col_1, str_1,trail
from (select col_1
,regexp_substr(dump(regexp_replace(col_1,' [0-9].[0-9]$','')),': (.)*') str_1
,regexp_substr(col_1,' [0-9.]+$') as trail
from test_022) t
order by str_1,trail desc;
|
|
|
|
Re: Sorting logic not working for all data conditions [message #360174 is a reply to message #360102] |
Wed, 19 November 2008 23:52   |
 |
ebrian
Messages: 2794 Registered: April 2006
|
Senior Member |
|
|
JRowbottom, seems that some of the ordering is off with your queries ?
SQL> select col_1
2 from (select col_1
3 ,to_number(trim(regexp_substr(col_1,'(^| )[0-9]*'))) as num1
4 ,regexp_substr(col_1,'(^| )[^ ]*') as alpha1
5 ,to_number(trim(regexp_substr(col_1,'(^| )[0-9]*',1,2))) as num2
6 ,regexp_substr(col_1,'(^| )[^ ]*',1,2) as alpha2
7 ,regexp_substr(col_1,' [0-9.]+$') as trail
8 from test_022) t
9 order by t.num1 desc nulls last
10 , t.alpha1 nulls last
11 , t.num2 desc nulls last
12 , t.alpha2 nulls last
13 , t.trail desc nulls last;
COL_1
----------------------------------------
12000 Series Router
500 Series Router
490 Series Router
450 Series Router
1 XXXXXX
ABC CRS-1 8-Slot Line-Card Chassis
ABC CRS-1 Line Card Chassis (Multi)
ABC CRS-1 1-Slot Line-Card Chassis
ABC CRS-1 Fabric-Card Chassis
ABC CRS-1 16-Slot Line-Card Chassis
ABC TelePresence Manager Release 3
ABC TelePresence Manager Release 10.25
ABC TelePresence Manager Release 1.3
ABC TelePresence Manager Release 1.2
ABC TelePresence Manager Release 1.1
ABC TelePresence Manager Release 1
ABC TelePresence Manager Release ()
ABC XR 12000 Series Router
ABC XR 12404 Router
ABC XR <XXXX> Router ()
20 rows selected.
SQL> select col_1
2 from (select col_1
3 ,regexp_substr(dump(regexp_replace(col_1,' [0-9].[0-9]$','')),': (.)*') str_1
4 ,regexp_substr(col_1,' [0-9.]+$') as trail
5 from test_022) t
6 order by str_1,trail desc;
COL_1
----------------------------------------
1 XXXXXX
12000 Series Router
450 Series Router
490 Series Router
500 Series Router
ABC CRS-1 1-Slot Line-Card Chassis
ABC CRS-1 16-Slot Line-Card Chassis
ABC CRS-1 8-Slot Line-Card Chassis
ABC CRS-1 Fabric-Card Chassis
ABC CRS-1 Line Card Chassis (Multi)
ABC TelePresence Manager Release 1.3
ABC TelePresence Manager Release 1.2
ABC TelePresence Manager Release 1.1
ABC TelePresence Manager Release ()
ABC TelePresence Manager Release 1
ABC TelePresence Manager Release 10.25
ABC TelePresence Manager Release 3
ABC XR 12000 Series Router
ABC XR 12404 Router
ABC XR <XXXX> Router ()
20 rows selected.
However, there are probably going to be so many different permutations in the data that it may not be probable to accommodate them all. Here was one I came up with, but I'm not sure where the last record "ABC XR <XXXX> Router ()" is really supposed to fit in ??
SQL> select * from test_022
2 order by to_number(regexp_substr(col_1, '^\d+')) desc nulls last,
3 regexp_replace(col_1,'\d|(..\(\)$)|([^ ]*\d)$'),
4 to_number(regexp_substr(col_1, ' \d*\.*\d+')) desc;
COL_1
----------------------------------------
12000 Series Router
500 Series Router
490 Series Router
450 Series Router
1 XXXXXX
ABC CRS-1 16-Slot Line-Card Chassis
ABC CRS-1 8-Slot Line-Card Chassis
ABC CRS-1 1-Slot Line-Card Chassis
ABC CRS-1 Fabric-Card Chassis
ABC CRS-1 Line Card Chassis (Multi)
ABC TelePresence Manager Release ()
ABC TelePresence Manager Release 10.25
ABC TelePresence Manager Release 3
ABC TelePresence Manager Release 1.3
ABC TelePresence Manager Release 1.2
ABC TelePresence Manager Release 1.1
ABC TelePresence Manager Release 1
ABC XR 12404 Router
ABC XR 12000 Series Router
ABC XR <XXXX> Router ()
20 rows selected.
In the end, PL/SQL may be more efficient ?
|
|
|
Re: Sorting logic not working for all data conditions [message #360488 is a reply to message #360061] |
Fri, 21 November 2008 02:51   |
J1357
Messages: 33 Registered: November 2008
|
Member |
|
|
But the above codes will not give correct results when
1) there is a special character such as '-().+' inside a string
2) when alphabet is attached with numeric such as '12A'
3) when space is occuring between 2 numeric characters '12A 123'
Like that...
The logic to be implemented is :
Numeric first and alphabetical sort next
For each word in the string - check if numerically it is possible to sort the strings
- followed by an alphabetic sort.
If there are same words - then repeat with the next word.
Can this be achieved using a Sequel command or write a Pl code for the same.
|
|
|
|
|
Goto Forum:
Current Time: Thu Feb 06 23:23:20 CST 2025
|