I have a sort order question

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 SQL Server 2005?
This info is needed for comparison sake between IBM mainframe world and Oracle world.
algotubeAsked:
Who is Participating?
 
8080_DiverCommented:
Your results appear to be sorted in ASCending order.

The real problem that you are going to face in trying to compare the two sets of results is that your buddy, who is in the IBM mainframe world, is working with an EBCDIC character set and you are working with an ASCII character set.  In the EBCDIC worlD, the characters for numbers come after the characters for letters while the ASCII sort order has the numbers before the letters.  So, whether you both sort in an descending order or an ascending order, your results will differ.  

The results will look like the following:

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
 
knightEknightCommented:
I'm not sure what you are asking.  Can't you just use ORDER BY col DESC to find out?

select top 100 *
from myTable
ORDER BY DataString DESC
0
 
algotubeAuthor Commented:

This is what I received when I ran the query in SQL Server

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

This is what my buddy received when he ran it in IBM mainframe world

 
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


Why the difference................?????




0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
knightEknightCommented:
Is there a leading space on some of the strings?  Try using LTRIM to get rid of the space:  

-- SQL Server
select SEQ, LTRIM(DataString)
from myTable
ORDER BY LTRIM(DataString) DESC
0
 
Anthony PerkinsCommented:
You will have to find a common collation in order to sort the same way.
0
 
Anthony PerkinsCommented:
So for example you could use something like this to change the sort order to match the mainframe:
ORDER BY YourColumn COLLATE SQL_EBCDIC037_CP1_CS_AS
0
 
algotubeAuthor Commented:
8080_Diver:  

Do you still have the query you used......if you do can you please post.  

Thank you so much for your help
0
 
8080_DiverCommented:
algotube,

I must confess, I didn't use a query but, rather, just my knowledge of how the results would come out.

However, the query has already been posted (see response #35507991):
SELECT SEQ
      ,LTRIM(DataString)
FROM myTable
ORDER BY LTRIM(DataString) DESC

Open in new window


 In looking at the output you say you got, though, it would appear that you omitted the ORDER BY LTRIM(DataString) DESC line.

0
 
Anthony PerkinsCommented:
Please find below a complete working example, showing the difference when you use ASCII as opposed to EBCDIC sort.  

I do notice something that does not appear correct from your results.  You state:
This is what my buddy received when he ran it in IBM mainframe world

 
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


However those last two rows appear to be reversed.  They should be as shown in my output.

DECLARE @YourTable TABLE (
		SEQ integer,
		Data varchar(50)
		)

SET NOCOUNT ON

INSERT @YourTable (SEQ, Data)		
VALUES (1, '2011-01-12-00.00.00.000000')
INSERT @YourTable (SEQ, Data)		
VALUES (2, '2011-01-12-XX.00.00.000000')
INSERT @YourTable (SEQ, Data)		
VALUES (3, '2011-01-12-XX-00.00.999999')
INSERT @YourTable (SEQ, Data)		
VALUES (4, '2011-01-12-00.00.00.999999')

SELECT	*
FROM	@YourTable
ORDER BY 
	Data DESC
		
SELECT	*
FROM	@YourTable
ORDER BY 
	Data COLLATE SQL_EBCDIC037_CP1_CS_AS DESC

Here is the output:
SEQ	Data
2	2011-01-12-XX.00.00.000000
3	2011-01-12-XX-00.00.999999
4	2011-01-12-00.00.00.999999
1	2011-01-12-00.00.00.000000

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

Open in new window

0
 
algotubeAuthor Commented:
Guys I can't thank you enough for all your replies, very very much appreciated
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.