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 Go to next message
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 #360065 is a reply to message #360061] Wed, 19 November 2008 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for posting sample data but we can't with. Posting a test case is posting create table and insert statements along with the result you want with the data you provide.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Sorting logic not working for all data conditions [message #360069 is a reply to message #360065] Wed, 19 November 2008 07:49 Go to previous messageGo to next message
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 #360074 is a reply to message #360069] Wed, 19 November 2008 07:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Hope I've not missed any thing.

Quote:
Posting a test case is posting create table and insert statements along with the result you want with the data you provide.

Regards
Michel
Re: Sorting logic not working for all data conditions [message #360075 is a reply to message #360074] Wed, 19 November 2008 08:11 Go to previous messageGo to next message
J1357
Messages: 33
Registered: November 2008
Member
Michael,

Yes ..I'm sorry to have put up the test case which has offended the rules.Consider this as my first post.Also, I agree that my code was not intended.
But I need whether such a thing can happen in SQL or we have to write a PL-SQL code for the same .
Re: Sorting logic not working for all data conditions [message #360080 is a reply to message #360075] Wed, 19 November 2008 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm sorry to have put up the test case which has offended the rules.Consider this as my first post

There is no problem and you can easily correct this posting a test case now.

Regards
Michel
Re: Sorting logic not working for all data conditions [message #360102 is a reply to message #360075] Wed, 19 November 2008 11:00 Go to previous messageGo to next message
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 #360107 is a reply to message #360102] Wed, 19 November 2008 11:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
He posted enough test data for me.

Data are there but not statements.

Regards
Michel
Re: Sorting logic not working for all data conditions [message #360174 is a reply to message #360102] Wed, 19 November 2008 23:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Sorting logic not working for all data conditions [message #360591 is a reply to message #360488] Fri, 21 November 2008 09:02 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I tell ya what...why don't you give TRUE representative data if you want folks to provide FREE support to you ??
Re: Sorting logic not working for all data conditions [message #360595 is a reply to message #360591] Fri, 21 November 2008 09:17 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I strongly suspect that we can do it with the following order by:
ORDER BY dump(rpad(col_1,100))

or, if you want it to be case insensitive:
ORDER BY dump(rpad(upper(col_1),100))

Previous Topic: replacing single quote while loading data
Next Topic: After Trigger is not working .
Goto Forum:
  


Current Time: Mon Dec 05 20:58:27 CST 2016

Total time taken to generate the page: 0.12706 seconds