CODEWITHSUNDEEP
sqlexcelsortingdb2sql-order-by
RVD5Star
RVD5Star

Reputation: 47

ORDER BY in DB2 SQL Query to match sort in Excel

I have a DB2 SQL query pulling an ID field (let's call it [ID]) that needs to be sorted in the way excel sorts ascending, for the purpose of speeding up lookup formulas that happen later in the process of my workflow.

SELECT [ID] FROM [TABLE] ORDER BY [ID]

My issue is that the DB2 code is sorting the data with the '01~010000~AAAAAB...', '01~010000~AAAAAC...', etc. rows to the top, and excel is sorting them to the bottom, below the '01~010000~000001...' rows, which is what I need for my lookup formulas to operate correctly.

So DB2 is sorting like this -

01~010000~AAAAAB~100000000000~01~01  
01~010000~AAAAAC~000000000001~01~01  
01~010000~ZAAAAA~000000000001~01~01  
01~010000~000001~000000000001~01~01  
01~010000~000001~000000000001~01~01

And Excel is sorting like this - 

01~010000~000001~000000000001~01~01  
01~010000~000001~000000000001~01~01  
01~010000~AAAAAB~100000000000~01~01  
01~010000~AAAAAC~000000000001~01~01  
01~010000~ZAAAAA~000000000001~01~01

Is there something I can do with my DB2 SQL in the ORDER BY to match how Excel sorts the data?

EDIT: To answer the question below, I'm afraid I don't have all of that technical information. I think it's z/os, but I don't know the answer to any of that other stuff. Any time I have had to google for answers about DB2 SQL, the examples work, so I guess whatever the rest of the world is using is what I have?

Upvotes: 0

Views: 686

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12314

It looks like the EBCDIC collation sequence sort order is used.
Try to sort by the COLLATION_KEY function with some appropriate collation-name constant as 2-nd argument.

Upvotes: 1

Related Questions