?
Solved

I have a sort order question

Posted on 2011-05-02
10
Medium Priority
?
293 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:algotube
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35507710
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
 

Author Comment

by:algotube
ID: 35507844

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
 
LVL 33

Assisted Solution

by:knightEknight
knightEknight earned 200 total points
ID: 35507991
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 22

Accepted Solution

by:
8080_Diver earned 400 total points
ID: 35508042
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35509899
You will have to find a common collation in order to sort the same way.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35509926
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
 

Author Comment

by:algotube
ID: 35516161
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35516430
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 400 total points
ID: 35545349
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
 

Author Closing Comment

by:algotube
ID: 35690526
Guys I can't thank you enough for all your replies, very very much appreciated
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

850 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