From: SGreen
Date: July 28 2004 4:06pm
Subject: RE: Using function followed by "order by function_name(table.field_name)".....
List-Archive: http://lists.mysql.com/mysql/169900
Message-Id:
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="=_alternative 00589E6285256EDF_="
--=_alternative 00589E6285256EDF_=
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
Scott,
I really don't understand your need to convert your string values to some=20
kind of numeric equivalent. When it comes to sorting strings it all=20
depends on the collation sequence for the characterset you are using.=20
For example, my STATUS; command tells me:
mysql Ver 14.3 Distrib 4.1.1a-alpha, for Win95/Win98 (i32)
Connection id: 1
Current database: test
Current user: sgreen@localhost
SSL: Not in use
Using delimiter: ;
Server version: 4.1.1a-alpha-nt-log
Protocol version: 10
Connection: localhost via TCP/IP
Client characterset: latin1=5Fswedish=5Fci
Server characterset: latin1=5Fswedish=5Fci
TCP port: 3306
Uptime: 2 hours 59 min 30 sec
Threads: 1 Questions: 19 Slow queries: 0 Opens: 12 Flush tables: 1=20
Open tables: 5 Queries per second avg: 0.002
(This is a development machine that has seen very little work yet today)
My "Server charset" is "latin1=5Fswedish=5Fci". From the source code for=20
MySQL, the ASCII collation order (sorting sequence) for that character set =
is:
static uchar sort=5Forder=5Flatin1[] =3D {
0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,
16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31,
32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47,
48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63,
64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79,
80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95,
96, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79,
80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90,123,124,125,126,127,
128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,
144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,
160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,
176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,
65, 65, 65, 65, 92, 91, 92, 67, 69, 69, 69, 69, 73, 73, 73, 73,
68, 78, 79, 79, 79, 79, 93,215,216, 85, 85, 85, 89, 89,222,223,
65, 65, 65, 65, 92, 91, 92, 67, 69, 69, 69, 69, 73, 73, 73, 73,
68, 78, 79, 79, 79, 79, 93,247,216, 85, 85, 85, 89, 89,222,255
};
You can see that the characters that represent the numbers "0" through "9" =
(positions 48 through 57) are sorted in order and before the capital=20
letters "A" through "Z" (65 through 90). The lower-case letters "a"=20
through "z" are assigned the same sort value as their capital counterparts =
(positions 97 through 122). This means that "ADAM" and "adam" will sort=20
together (the collation order is case-insensitive, thus the "ci" at the=20
end of the name)
That also means that "00 apple" will sort before "01 apple" and that "1=20
cherry" and "10 cherry" will sort before "2 cherry" ("1" comes=20
*alphabetically* before "2"). This is because the numbers do not exist as=20
separate entities within a string. To the CPU, all of the elements of a=20
string are viewed as numeric values (their ASCII or UNICODE values) that=20
get sorted according to the rules built into the characterset being used=20
(see table above).
So, please try to explain again why an ORDER BY fieldname (without any=20
sort of conversion function on your field) does not work as you want it=20
to.
Thank you for your patience with me,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Scott Fletcher" wrote on 07/28/2004 10:42:39 AM:
> Okay, redo(ed) the scripts and eliminated all of the CHAR() part=20
> from all of the SQL Syntax in the PHP scripts and it look better.=20
> What functions can I use to substitute the INTEGER() and DOUBLE()=20
> from DB2 to MySQL?. I?ll keep searching the MySQL but I don?t seem=20
> to find a MySQL function that would do something similar to this.=20
> Here I got by the ?ORDER BY field=5Fname ASC? for the integer value?
>=20
> --snip?
> 10
> 11
> 129
> 21
> 23
> --snip?
>=20
> Where 129 should be at the bottom in integer value. Similar concept
> with the double or float value?
>=20
> Thanks,
> FletchSOD
>=20
>=20
> From: SGreen@stripped [mailto:SGreen@stripped]=20
> Sent: Wednesday, July 28, 2004 8:56 AM
> To: Scott Fletcher
> Cc: mysql@stripped
> Subject: RE: Using function followed by "order by=20
> function=5Fname(table.field=5Fname)".....
>=20
>=20
> Let me quote a little bit from the docs (http://dev.mysql.
> com/doc/mysql/en/String=5Ffunctions.html):=20
>=20
> CHAR() interprets the arguments as integers and returns a string=20
> consisting of the characters given by the code values of those=20
> integers. NULL values are skipped.=20
>=20
> mysql> SELECT CHAR(77,121,83,81,'76');=20
> -> 'MySQL'=20
> mysql> SELECT CHAR(77,77.3,'77.3');=20
> -> 'MMM'=20
>=20
> That means that when it processes each of the fields in your=20
> resultset, it converts that string into some other string and is=20
> sorting by the results of that transformation. Definitely NOT what=20
> you wanted. Please, let's try again. Please, show me the results of=20
> just a plain "ORDER BY field=5Fname" query (without the CHAR()=20
> function) and explain to me what is wrong...=20
>=20
> Yours,=20
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine=20
>=20
> "Scott Fletcher" wrote on 07/28/2004 09:12:45 AM:
>=20
> > The example of ORDER BY that didn?t solve the problem is ?=20
> >=20
> > --snip--=20
> > Debug Test (WM Account) 1
> > ABC Company
> > Riverknoll A C
> > SDFONE'sdf
> > SeaWater=20
> > --snip--=20
> >=20
> > which should be ?=20
> >=20
> > --snip--=20
> > ABC Company
> > Debug Test (WM Account) 1
> > Riverknoll A C
> > SDFONE'sdf
> > SeaWater=20
> > --snip--=20
> >=20
> > The word, ?Debug Test (WM Account) 1? came before the ?ABC Company?=20
> > which should be the other way around?. This show that the ?ORDER BY
> > CHAR(??)? doesn?t work. I just don?t see how to use a function that
> > would sort the char in ascending order properly?=20
> >=20
> > Thanks,=20
> > FletchSOD=20
> >=20
> >=20
> >=20
> > From: SGreen@stripped [mailto:SGreen@stripped]=20
> > Sent: Wednesday, July 28, 2004 7:58 AM
> > To: Scott Fletcher
> > Cc: mysql@stripped
> > Subject: Re: Using function followed by "order by=20
> > function=5Fname(table.field=5Fname)".....=20
> >=20
> >=20
> > I hope you have already tried just plain-old "ORDER BY field=5Fname"=20
> > in your query. What is wrong with the default order presented? Can=20
> > you show an example of how the ORDER BY did not solve your problem=20
> > along with an example of what it should have been for your situation?=20
> >=20
> > Thanks in advance,=20
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine=20
> >=20
> >=20
> > "Scott Fletcher" wrote on 07/27/2004 05:29:05=20
PM:
> >=20
> > > I'm having a little bit of a trouble with the use of the SQL=20
function...
> > > What I have here is a webpage that show the row of data, the web=20
user
> > > get to click on the field to sort the row by the field name. So,=20
when I
> > > use "order by char(field=5Fname)", the data doesn't turned out right =
in
> > > alpha-numeric in ascending order...
> > >=20
> > >=20
> > >=20
> > > --snip--
> > >=20
> > > Debug Test (WM Account) 1
> > >=20
> > > ABC Company
> > >=20
> > > Riverknoll A C
> > >=20
> > > SDFONE'sdf
> > >=20
> > > SeaWater
> > >=20
> > > --snip--
> > >=20
> > >=20
> > >=20
> > > I would like the first few letter of each row be in correct
> > > alpha-numeric order regardless of what character is there in the=20
data,
> > > like (, ), ', ?, etc...
> > >=20
> > >=20
> > >=20
> > > Thanks,
> > >=20
> > > FletchSOD
> > >=20
--=_alternative 00589E6285256EDF_=--