?
Solved

I have a sort order question

Posted on 2011-05-02
5
Medium Priority
?
478 Views
Last Modified: 2013-12-18
The following table has a series of text fields that I need to return in descending order.

Seq      Data String
1      2011-01-12-00.00.00.000000
2      2011-01-12-XX.00.00.000000
3      2011-01-12-XX-00.00.999999
4      2011-01-12-00.00.00.999999

How do I find out the sequence that these will be returned in descending order in Oracle world?
This info is needed for comparison sake between IBM mainframe world and Oracle and SQL Server
0
Comment
Question by:algotube
5 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 35507717
select seq,data_string from your_table
order by seq desc;

or

select seq,data_string from your_table
order by data_string desc;

depending on which column you want to determine the order
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35507726
Oracle will order string using a simple ASCII value.

Since those are dates (with the exception of 'XX'), you can use the TO_TIMESTAMP function to convert them to an Oracle timestamp data type then order by that.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 35507748
>>> will order string using a simple ASCII value.

actually,  your NLS_LANG and NLS_SORT parameters will determine the ordering, but since most of your data is numeric, it should be the same for most (if not all) languages.


you can't convert ' 2011-01-12-XX.00.00.000000' to a timestamp
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 600 total points
ID: 35508069
AS I said in reply to your other (identical) question in the SQL Server 2005 cataegory:

Your real problem is going to be the difference between the EBCDIC character set (which is what your mainframe buddy is going to be working with) and the ASCII character set (which is what you will be working with).  In the EBCDIC world, letters come before numbers but in the ASCII world, numbers come before letters.  That will mean that, even though you both sort the results in descending order, you will have different looking results:

Your buddy's results:
SORTED DESCENDING:
SEQ   DATA STRING
4     2011-01-12-00.00.00.999999
1     2011-01-12-00.00.00.000000
3     2011-01-12-XX-00.00.999999
2     2011-01-12-XX.00.00.000000

Open in new window


Your results:
SORTED DESCENDING:
SEQ   DATA STRING
3     2011-01-12-XX-00.00.999999
2     2011-01-12-XX.00.00.000000
4     2011-01-12-00.00.00.999999
1     2011-01-12-00.00.00.000000

Open in new window

0
 

Author Closing Comment

by:algotube
ID: 35690552
Thank you for your replies and help
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month14 days, 22 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question