Order By Question...

[sindiglo]

Hello,

Can anyone help me with how to get an alphanumeric query result to order correctly.
This is the query I am using right now:

$sql = "SELECT * FROM teams where team_div='B' order by team_num +0";

with this result:

TEAM ID TEAM NAME
C4 (B4) Thrown Together
C6 (B5) Smashers
C1 (B9) Six-Cess
C3 (B10) The Mandelbaums
C5 (B13) Ocelots
C10 (B14) Set To Go
C9 (B15) SpikeVB's
C2 (B16) Serve it Up
C7 (B17) IND B-1
C8 (B18) IND B-2

Any suggestions? (Php4, Mysql 3.51) All fields are text strings.

I was told that if I added the +0 that it would order the list by the Team ID, but it doed not work.

visit http://www.hhmvl.com/c_division.php to see it live!

[Xnuiem]

a text field will not order like that, only a number type field.

Or you can use the various functions like cast() within mysql to break it up and order.

[sindiglo]

I think I have it!

SELECT * FROM teams where team_div='C' order by SUBSTRING(team_num,2)+0

Substring selects the remaining characters starting at the second. Then I convert the string to an integer by adding 0.

Bingo!

B) B) B)