Sorting weirdness? - DB2 Database

apattin

HI all,

Can someone explain this sorting issue?

we are using V8 on Windows, but database *might* have been created
with V7 (I can find out if it really matters)

I have a table with one column, data VARCHAR(255) . It contains three
values : 1729_at, 1773_at and 177_at.

If I issue this SQL:

SELECT * FROM mytable ORDER BY data

I get:

1729_at
177_at
1773_at

This seems to imply that in the active collating sequence, whatever it
happens to be, the '_' character comes before the '3' character. Am I
right? Why is that? In ASCII (and therefore also in UTF-8, I think)
the 3 comes before the _. In EBCDIC, the _ comes before the 3, but is
this at all possible, that someone created the database using EBCDIC
on Windows?

If I do the same sort in Java (uses Unicode) I get the right result,
ie

1729_at
1773_at
177_at

What am I missing?

Thanks,

Alejandrina

Jun 6 '07 #1

Subscribe Post Reply

6474

Lennart

apattin wrote:

HI all,

Can someone explain this sorting issue?

we are using V8 on Windows, but database *might* have been created
with V7 (I can find out if it really matters)

I have a table with one column, data VARCHAR(255) . It contains three
values : 1729_at, 1773_at and 177_at.

If I issue this SQL:

SELECT * FROM mytable ORDER BY data

I get:

1729_at
177_at
1773_at

This seems to imply that in the active collating sequence, whatever it
happens to be, the '_' character comes before the '3' character. Am I
right? Why is that? In ASCII (and therefore also in UTF-8, I think)
the 3 comes before the _. In EBCDIC, the _ comes before the 3, but is
this at all possible, that someone created the database using EBCDIC
on Windows?

If I do the same sort in Java (uses Unicode) I get the right result,
ie

1729_at
1773_at
177_at

What am I missing?

I'm also interested in why this is the case. I've tested 3 different
databases (2 V8, 1 V9) with different codepage etc. All databases
created in V8:

LUW V8 fixpak 14:

Database territory = SE
Database code page = 1252
Database code set = IBM-1252
Database country/region code = 46
Database collating sequence = UNIQUE
Alternate collating sequence (ALT_COLLATE) =

db2 "with T (data) as (values ('1729_at'),('177_at'),('1773_at')) select
data, ascii(substr(data,3,1)), ascii(substr(data,4,1)) from T order by data"

DATA 2 3
------- ----------- -----------
1729_at 50 57
177_at 55 95
1773_at 55 51
LUV V8 fixpak 13:

Database territory = SE
Database code page = 819
Database code set = ISO8859-1
Database country/region code = 46
Database collating sequence = UNIQUE
Alternate collating sequence (ALT_COLLATE) =

db2 "with T (data) as (values ('1729_at'),('177_at'),('1773_at')) select
data, ascii(substr(data,3,1)), ascii(substr(data,4,1)) from T order by data"

DATA 2 3
------- ----------- -----------
1729_at 50 57
177_at 55 95
1773_at 55 51
LUW V9 fixpak 0:

Database territory = C
Database code page = 1208
Database code set = UTF-8
Database country/region code = 1
Database collating sequence = UCA400_NO
Alternate collating sequence (ALT_COLLATE) =

db2 "with T (data) as (values ('1729_at'),('177_at'),('1773_at')) select
data, ascii(substr(data,3,1)), ascii(substr(data,4,1)) from T order by data"

DATA 2 3
------- ----------- -----------
1729_at 50 57
177_at 55 95
1773_at 55 51
/Lennart

Jun 6 '07 #2

Brian Tkatch

On Wed, 06 Jun 2007 22:01:15 +0200, Lennart
<er******************@gmail.comwrote:

>apattin wrote:
>HI all,

Can someone explain this sorting issue?

we are using V8 on Windows, but database *might* have been created
with V7 (I can find out if it really matters)

I have a table with one column, data VARCHAR(255) . It contains three
values : 1729_at, 1773_at and 177_at.

If I issue this SQL:

SELECT * FROM mytable ORDER BY data

I get:

1729_at
177_at
1773_at

This seems to imply that in the active collating sequence, whatever it
happens to be, the '_' character comes before the '3' character. Am I
right? Why is that? In ASCII (and therefore also in UTF-8, I think)
the 3 comes before the _. In EBCDIC, the _ comes before the 3, but is
this at all possible, that someone created the database using EBCDIC
on Windows?

If I do the same sort in Java (uses Unicode) I get the right result,
ie

1729_at
1773_at
177_at

What am I missing?

I'm also interested in why this is the case. I've tested 3 different
databases (2 V8, 1 V9) with different codepage etc. All databases
created in V8:

LUW V8 fixpak 14:

Database territory = SE
Database code page = 1252
Database code set = IBM-1252
Database country/region code = 46
Database collating sequence = UNIQUE
Alternate collating sequence (ALT_COLLATE) =

db2 "with T (data) as (values ('1729_at'),('177_at'),('1773_at')) select
data, ascii(substr(data,3,1)), ascii(substr(data,4,1)) from T order by data"

DATA 2 3
------- ----------- -----------
1729_at 50 57
177_at 55 95
1773_at 55 51
LUV V8 fixpak 13:

Database territory = SE
Database code page = 819
Database code set = ISO8859-1
Database country/region code = 46
Database collating sequence = UNIQUE
Alternate collating sequence (ALT_COLLATE) =

db2 "with T (data) as (values ('1729_at'),('177_at'),('1773_at')) select
data, ascii(substr(data,3,1)), ascii(substr(data,4,1)) from T order by data"

DATA 2 3
------- ----------- -----------
1729_at 50 57
177_at 55 95
1773_at 55 51
LUW V9 fixpak 0:

Database territory = C
Database code page = 1208
Database code set = UTF-8
Database country/region code = 1
Database collating sequence = UCA400_NO
Alternate collating sequence (ALT_COLLATE) =

db2 "with T (data) as (values ('1729_at'),('177_at'),('1773_at')) select
data, ascii(substr(data,3,1)), ascii(substr(data,4,1)) from T order by data"

DATA 2 3
------- ----------- -----------
1729_at 50 57
177_at 55 95
1773_at 55 51
/Lennart

To see the order:

DECLARE GLOBAL TEMPORARY TABLE Charmap (Num INT, Digit CHAR(1))

BEGIN ATOMIC
DECLARE A INT DEFAULT 0;
WHILE A < 257 DO
INSERT INTO
SESSION.Charmap(Num, Digit)
VALUES (A, CHR(A));
SET A = A + 1;
END WHILE;
END
SELECT Num, Digit FROM SESSION.Charmap ORDER BY Num
SELECT Num, Digit FROM SESSION.Charmap ORDER BY Digit

DROP TABLE SESSION.Charmap

The ORDER BY Digit will show the order used.

B.

Jun 6 '07 #3

Brian Tkatch

On Wed, 06 Jun 2007 10:38:34 -0700, apattin <ap*****@gmail.comwrote:

>HI all,

Can someone explain this sorting issue?

we are using V8 on Windows, but database *might* have been created
with V7 (I can find out if it really matters)

I have a table with one column, data VARCHAR(255) . It contains three
values : 1729_at, 1773_at and 177_at.

If I issue this SQL:

SELECT * FROM mytable ORDER BY data

I get:

1729_at
177_at
1773_at

This seems to imply that in the active collating sequence, whatever it
happens to be, the '_' character comes before the '3' character. Am I
right? Why is that? In ASCII (and therefore also in UTF-8, I think)
the 3 comes before the _. In EBCDIC, the _ comes before the 3, but is
this at all possible, that someone created the database using EBCDIC
on Windows?

If I do the same sort in Java (uses Unicode) I get the right result,
ie

1729_at
1773_at
177_at

What am I missing?

Thanks,

Alejandrina

If you want to ORDER BY the ASCII values, use ORDER BY ASCII(data);

Otherwise, a different order is used.

B.

Jun 6 '07 #4

Lennart

Brian Tkatch wrote:

On Wed, 06 Jun 2007 10:38:34 -0700, apattin <ap*****@gmail.comwrote:

>>HI all,

Can someone explain this sorting issue?

we are using V8 on Windows, but database *might* have been created
with V7 (I can find out if it really matters)

I have a table with one column, data VARCHAR(255) . It contains three
values : 1729_at, 1773_at and 177_at.

If I issue this SQL:

SELECT * FROM mytable ORDER BY data

I get:

1729_at
177_at
1773_at

This seems to imply that in the active collating sequence, whatever it
happens to be, the '_' character comes before the '3' character. Am I
right? Why is that? In ASCII (and therefore also in UTF-8, I think)
the 3 comes before the _. In EBCDIC, the _ comes before the 3, but is
this at all possible, that someone created the database using EBCDIC
on Windows?

If I do the same sort in Java (uses Unicode) I get the right result,
ie

1729_at
1773_at
177_at

What am I missing?

Thanks,

Alejandrina

If you want to ORDER BY the ASCII values, use ORDER BY ASCII(data);

Otherwise, a different order is used.

I get the same sortorder using order by ascii(data)

db2 "with T (data) as (values ('1729_at'),('177_at'),('1773_at')) select
* from T order by ascii(data)"

DATA
-------
1729_at
177_at
1773_at

Jun 6 '07 #5

Lennart

Brian Tkatch wrote:
[...]

To see the order:

DECLARE GLOBAL TEMPORARY TABLE Charmap (Num INT, Digit CHAR(1))

BEGIN ATOMIC
DECLARE A INT DEFAULT 0;
WHILE A < 257 DO
INSERT INTO
SESSION.Charmap(Num, Digit)
VALUES (A, CHR(A));
SET A = A + 1;
END WHILE;
END
SELECT Num, Digit FROM SESSION.Charmap ORDER BY Num
SELECT Num, Digit FROM SESSION.Charmap ORDER BY Digit

DROP TABLE SESSION.Charmap

The ORDER BY Digit will show the order used.

B.

Thanks, another variant without a session table:

with charmap(num, digit) as (values (30,chr(30)) union all select num+1,
chr(num+1) from charmap where num < 100) select * from charmap order by
digit

I used a smaller interval to avoid scrambling of the screen

Jun 6 '07 #6

apattin

On Jun 6, 5:55 pm, Brian Tkatch <N/Awrote:

On Wed, 06 Jun 2007 22:01:15 +0200, Lennart

<erik.lennart.jons...@gmail.comwrote:
apattin wrote:
HI all,

Can someone explain this sorting issue?

we are using V8 on Windows, but database *might* have been created
with V7 (I can find out if it really matters)

I have a table with one column, data VARCHAR(255) . It contains three
values : 1729_at, 1773_at and 177_at.

If I issue this SQL:

SELECT * FROM mytable ORDER BY data

I get:

1729_at
177_at
1773_at

This seems to imply that in the active collating sequence, whatever it
happens to be, the '_' character comes before the '3' character. Am I
right? Why is that? In ASCII (and therefore also in UTF-8, I think)
the 3 comes before the _. In EBCDIC, the _ comes before the 3, but is
this at all possible, that someone created the database using EBCDIC
on Windows?

If I do the same sort in Java (uses Unicode) I get the right result,
ie

1729_at
1773_at
177_at

What am I missing?

I'm also interested in why this is the case. I've tested 3 different
databases (2 V8, 1 V9) with different codepage etc. All databases
created in V8:

LUW V8 fixpak 14:

Database territory = SE
Database code page = 1252
Database code set = IBM-1252
Database country/region code = 46
Database collating sequence = UNIQUE
Alternate collating sequence (ALT_COLLATE) =

db2 "with T (data) as (values ('1729_at'),('177_at'),('1773_at')) select
data, ascii(substr(data,3,1)), ascii(substr(data,4,1)) from T order by data"

DATA 2 3
------- ----------- -----------
1729_at 50 57
177_at 55 95
1773_at 55 51

LUV V8 fixpak 13:

Database territory = SE
Database code page = 819
Database code set = ISO8859-1
Database country/region code = 46
Database collating sequence = UNIQUE
Alternate collating sequence (ALT_COLLATE) =

db2 "with T (data) as (values ('1729_at'),('177_at'),('1773_at')) select
data, ascii(substr(data,3,1)), ascii(substr(data,4,1)) from T order by data"

DATA 2 3
------- ----------- -----------
1729_at 50 57
177_at 55 95
1773_at 55 51

LUW V9 fixpak 0:

Database territory = C
Database code page = 1208
Database code set = UTF-8
Database country/region code = 1
Database collating sequence = UCA400_NO
Alternate collating sequence (ALT_COLLATE) =

db2 "with T (data) as (values ('1729_at'),('177_at'),('1773_at')) select
data, ascii(substr(data,3,1)), ascii(substr(data,4,1)) from T order by data"

DATA 2 3
------- ----------- -----------
1729_at 50 57
177_at 55 95
1773_at 55 51

/Lennart

To see the order:

DECLARE GLOBAL TEMPORARY TABLE Charmap (Num INT, Digit CHAR(1))

BEGIN ATOMIC
DECLARE A INT DEFAULT 0;
WHILE A < 257 DO
INSERT INTO
SESSION.Charmap(Num, Digit)
VALUES (A, CHR(A));
SET A = A + 1;
END WHILE;
END

SELECT Num, Digit FROM SESSION.Charmap ORDER BY Num
SELECT Num, Digit FROM SESSION.Charmap ORDER BY Digit

DROP TABLE SESSION.Charmap

The ORDER BY Digit will show the order used.

B.

Thanks, Brian. I guess I want to know is why is this specific
collating sequence being used, as ASCII or Unicode appear to be
different.

Alejandrina

Jun 7 '07 #7

Tonkuma

Collating sequence is defined at the time of creating DATABASE and
can't change later.
CREATE DATABASE .....
COLLATE USING SYSTEM | COMPATIBILITY | IDENTITY | IDENTITY_16BIT |
UCA400_NO | UCA400_LTH | NLSCHAR

SYSTEM (Default)
Collating sequence based on the database territory. This option cannot
be specified when creating a Unicode database.

IDENTITY
Identity collating sequence, in which strings are compared byte for
byte.
------------------- Commands Entered --------------------
SELECT data, HEX(data)
FROM (VALUES '1729_at', '1773_at', '177_at' ) AS mytable(data)
ORDER BY HEX(data);
--------------------------------------------------------

DATA 2
------- --------------
1729_at 313732395F6174
1773_at 313737335F6174
177_at 3137375F6174

3 record(s) selected.

Jun 7 '07 #8

Brian Tkatch

On Thu, 07 Jun 2007 00:16:36 +0200, Lennart
<er******************@gmail.comwrote:

>Brian Tkatch wrote:
[...]
>To see the order:

DECLARE GLOBAL TEMPORARY TABLE Charmap (Num INT, Digit CHAR(1))

BEGIN ATOMIC
DECLARE A INT DEFAULT 0;
WHILE A < 257 DO
INSERT INTO
SESSION.Charmap(Num, Digit)
VALUES (A, CHR(A));
SET A = A + 1;
END WHILE;
END
SELECT Num, Digit FROM SESSION.Charmap ORDER BY Num
SELECT Num, Digit FROM SESSION.Charmap ORDER BY Digit

DROP TABLE SESSION.Charmap

The ORDER BY Digit will show the order used.

B.

Thanks, another variant without a session table:

with charmap(num, digit) as (values (30,chr(30)) union all select num+1,
chr(num+1) from charmap where num < 100) select * from charmap order by
digit

Silly me. I *still* haven't integrated recursive WITHs into my noggin.
Thanx for the lesson!

>
I used a smaller interval to avoid scrambling of the screen

Considering the order changes a lot, showing every character may be
advantageous.

B.

B.

Jun 7 '07 #9

Brian Tkatch

On Thu, 07 Jun 2007 00:08:09 +0200, Lennart
<er******************@gmail.comwrote:

>Brian Tkatch wrote:
>On Wed, 06 Jun 2007 10:38:34 -0700, apattin <ap*****@gmail.comwrote:

>>>HI all,

Can someone explain this sorting issue?

we are using V8 on Windows, but database *might* have been created
with V7 (I can find out if it really matters)

I have a table with one column, data VARCHAR(255) . It contains three
values : 1729_at, 1773_at and 177_at.

If I issue this SQL:

SELECT * FROM mytable ORDER BY data

I get:

1729_at
177_at
1773_at

This seems to imply that in the active collating sequence, whatever it
happens to be, the '_' character comes before the '3' character. Am I
right? Why is that? In ASCII (and therefore also in UTF-8, I think)
the 3 comes before the _. In EBCDIC, the _ comes before the 3, but is
this at all possible, that someone created the database using EBCDIC
on Windows?

If I do the same sort in Java (uses Unicode) I get the right result,
ie

1729_at
1773_at
177_at

What am I missing?

Thanks,

Alejandrina

If you want to ORDER BY the ASCII values, use ORDER BY ASCII(data);

Otherwise, a different order is used.

I get the same sortorder using order by ascii(data)

db2 "with T (data) as (values ('1729_at'),('177_at'),('1773_at')) select
* from T order by ascii(data)"

DATA
-------
1729_at
177_at
1773_at

My mistake. My example in the other thread used one character, and in
that case ASCII will obviously work. With more than one char, ASCII
returns only the first char's value, as the docs say "Returns the
ASCII code value of the leftmost character of the argument as an
integer." Which is pretty useless here.

Tonkuma's reply uses HEX(), which does an ASCII on all the chars,
which is what we want.

Thanx for the catch.

B.

Jun 7 '07 #10

Similar topics

Weirdness: (-0.666667 + 0.333333)

by: Corne' Cornelius | last post by:

Hi, I'm experiencing some weirdness in a program, when subtracting 2 (double)'s which should result in 0, but instead it returns -1.11022e-16. It looks to me that changing the double x_step...

C / C++

ListView Weirdness in TabControl?

by: (Pete Cresswell) | last post by:

TabControl on the right side of a form with two tabs: Tab #1 contains two subforms that show some dynamic query results. Tab #2 contains a ListView that gets dynamically populated as the user...

Microsoft Access / VBA

Weirdness with HTTPModule, HTTPHandler, IIS, Mappings

by: Bruce B | last post by:

Hi group, I'm experiencing some extreme weirdness over the last week with IIS related to it's Mappings and how .NET is behaving. I can't explain the behavior as it seems very random. Our...

ASP.NET

Weirdness... huh? (specified web server not running ASP.NET version 1.1)

by: VB Programmer | last post by:

My development machine has been working perfectly, writing ASP.NET apps, etc... I just went to open a project and it said: "Visual Studio .NET has detected that the specified web server is not...

ASP.NET

aspnet_wp.exe weirdness

by: Phil Weber | last post by:

I'm attempting to debug an ASP.NET Web application in VS.NET 2003. I'm running the app and the debugger on my local machine (Windows XP Professional). I am logged in as a local administrator. In...

ASP.NET

File security weirdness createing file.

by: David Thielen | last post by:

Hi; I am creating png files in my ASP .NET app. When I am running under Windows 2003/IIS 6, the file is not given the security permissions it should have. It does not have any permission for...

ASP.NET

Positioning Weirdness in IE6 and IE7

by: rhino | last post by:

I've got some positioning problems that I can't figure out. Can anyone help? My website was working fine in IE7 and the current releases of Firefox and Opera so I had a look at it in IE6 and...

HTML / CSS

Internet Explorer JavaScript Weirdness

by: Prisoner at War | last post by:

Hi, All: I have a JavaScript search engine that always causes MSIE 7 to do a top-of-page security "warning" (that top-of-page-bar, and not an "alert" )...but other websites' JavaScripts do not...

Javascript

xml.dom.minidom weirdness: bug?

by: JYA | last post by:

Hi. I was writing an xmltv parser using python when I faced some weirdness that I couldn't explain. What I'm doing, is read an xml file, create another dom object and copy the element from...

Python

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice