Previous Thread
Next Thread
Print Thread
Rate Thread
#312648 01/12/2007 11:20 AM
Joined: Sep 1999
Posts: 76
Power User
Power User
Offline
Joined: Sep 1999
Posts: 76
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
Myke #312652 01/12/2007 9:18 PM
Joined: Jan 2003
Posts: 3,456
Likes: 2
Master Hacker
Master Hacker
Offline
Joined: Jan 2003
Posts: 3,456
Likes: 2
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

Joined: Sep 1999
Posts: 76
Power User
Power User
Offline
Joined: Sep 1999
Posts: 76
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.
Myke #312659 01/13/2007 1:50 AM
Joined: Sep 1999
Posts: 76
Power User
Power User
Offline
Joined: Sep 1999
Posts: 76
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.


Link Copied to Clipboard
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
bluehost
InterServer
Visit us on Facebook
Member Spotlight
Bill B
Bill B
Issaquah, WA
Posts: 87
Joined: December 2001
Forum Statistics
Forums63
Topics37,574
Posts293,926
Members13,850
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,833
Greg Hard 4,625
Top Posters(30 Days)
Top Likes Received
isaac 82
Gizmo 20
Brett 7
WebGuy 2
Morgan 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2024 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20240506)