Previous Thread
Next Thread
Print Thread
Rate Thread
MySQL query sorting #312648
01/12/2007 11:20 AM
01/12/2007 11:20 AM
Joined: Sep 1999
Posts: 97
Sydney, Australia
M
Myke Offline OP
Power User
Myke  Offline OP
Power User
M
Joined: Sep 1999
Posts: 97
Sydney, Australia
I've got a field in a table of type VARCHAR(8). The various values contained in this field include positive and negative numbers, and plain text.

When I run a query, sorting on this field, it doesn't appear to sort correctly. In my ORDER BY I'm type casting this field as follows:

ORDER BY cast(MyField as signed)

Now, if the following values are found in this field all throughout my table:

6, 3, down, slam, stgr, 0, -3

and the query is run, I get something like:

6
3
down
slam
stgr
down
0
-3

I have no idea why this sorts weirdly. I mean, even multiple instances of the same text values (like "down") aren't even appearing together.

The only thing that I find suspicious is that the Collation for this field (and all fields actually) is latin1_swedish_ci, but when I tried changing it to something like utf8_general_ci, it makes no difference.

To see this live, check out: http://virtuafighter.com/commands/index.php?chara=akira&ver=5b&order=mc&sort=desc

It's basically a command list for a video game. Clicking on the column headings sorts on that column. If you click on the "MC" column, you'll see exactly what I mean with the weird results. Sorting on the "Dmg" column seems fine, however. This field is of the same type, but does not include any text values.

Any clues on how I can sort a field with a mixture of positive and negative numbers, and text, would be appreciated.

Sponsored Links
Re: MySQL query sorting [Re: Myke] #312652
01/12/2007 9:18 PM
01/12/2007 9:18 PM
Joined: Jan 2003
Posts: 3,765
Saint Johns, PA
I
Ian Spence Offline
Master Hacker
Ian Spence  Offline
Master Hacker
I
Joined: Jan 2003
Posts: 3,765
Saint Johns, PA
when you cast strings to integers, they are cast to 0 if they aren't numeric.

So your actual results with sorting were

6
3
0
0
0
0
0
-3


Stop casting them to integers and you should be good to go

Re: MySQL query sorting [Re: Ian Spence] #312655
01/12/2007 10:43 PM
01/12/2007 10:43 PM
Joined: Sep 1999
Posts: 97
Sydney, Australia
M
Myke Offline OP
Power User
Myke  Offline OP
Power User
M
Joined: Sep 1999
Posts: 97
Sydney, Australia
Thanks for the explanation.

If I don't cast, then, as an example, the number values will sort like this:

1
10
11
2
20
22

instead of:

1
2
10
11
20
22

Is there a way to correctly sort a VARCHAR field containing positive numbers, negative numbers and plain text?

Last edited by Myke; 01/12/2007 10:46 PM.
Re: MySQL query sorting [Re: Myke] #312659
01/13/2007 1:50 AM
01/13/2007 1:50 AM
Joined: Sep 1999
Posts: 97
Sydney, Australia
M
Myke Offline OP
Power User
Myke  Offline OP
Power User
M
Joined: Sep 1999
Posts: 97
Sydney, Australia
Found a solution to work.

I ended up using a CASE statement in my SELECT query to assign numerical values to my strings. It basically goes:

SELECT * FROM MyTable
ORDER BY
CASE MyField
WHEN 'string1' THEN 100
WHEN 'string2' THEN 200
and so on...
ELSE cast(MyField as signed)
END

Cheers.


Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with, and recommend, the following Web Hosts:
· Stable Host
· Blue Host
· Interserver.net
Visit Us on Facebook
Member Spotlight
Gizmo
Gizmo
Portland, OR, USA
Posts: 5,938
Joined: January 2000
Show All Member Profiles 
Forum Statistics
Forums64
Topics37,448
Posts293,484
Members13,793
Most Online1,498
Mar 17th, 2017
Top Posters(All Time)
AllenAyres 25,587
JoshPet 11,330
Rick 8,373
LK 7,396
Lord Dexter 6,503
Gizmo 5,938
Greg Hard 5,533
Top Posters(30 Days)
isaac 4
Today's Statistics
Currently Online 732
Topics Created 0
Posts Made 0
Users Online 2
Birthdays 21
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2018 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 7.6.2
(Preview build 20180611.dev)
Page Time: 0.034s Queries: 15 (0.009s) Memory: 3.2416 MB (Peak: 3.3783 MB) Zlib enabled. Server Time: 2018-06-22 07:29:39 UTC