Sorting correctly...

[kreeves]

I am running a long query that has a combination of results.

The results that I'm sorting by is the "Room Number". This room number
could be a number or even something like "Office". (This information
is used for schools, and their rooms aren't always labelled by true
numbers.).

The problem I am having is ... the numbers will not sort from actual
"highest to lowest". They go something like this: "1, 10, 11, 12, 13,
14, 15, 2, 20, 3, 4, 5, 6, 7, 8, Office, etc". I'd like to sort them
"ASCII-betically".

Is there anything in MySQL to assist me with this, or will I need to
code this in. This is an older project not written by myself and there
are many reworkings I'd have to do to get it to work as needed. So, a
quick addition to the SQL would be very nice.

Thank you.

[Gordon Burditt]

>The results that I'm sorting by is the "Room Number". This room number

could be a number or even something like "Office". (This information
is used for schools, and their rooms aren't always labelled by true
numbers.).

The problem I am having is ... the numbers will not sort from actual
"highest to lowest". They go something like this: "1, 10, 11, 12, 13,
14, 15, 2, 20, 3, 4, 5, 6, 7, 8, Office, etc". I'd like to sort them
"ASCII-betically".

Given the most reasonable definition of "ASCII-betically" I can
come up with, that list *IS* sorted (as a bunch of strings). If
you think it isn't, explain why not, and what you DO want.

Gordon L. Burditt

[Bill Karwin]

kr*****@gmail.com wrote:

The results that I'm sorting by is the "Room Number". This room number
could be a number or even something like "Office". (This information
is used for schools, and their rooms aren't always labelled by true
numbers.).

The problem I am having is ... the numbers will not sort from actual
"highest to lowest". They go something like this: "1, 10, 11, 12, 13,
14, 15, 2, 20, 3, 4, 5, 6, 7, 8, Office, etc". I'd like to sort them
"ASCII-betically".

Try something like this:

SELECT room_num FROM myTable
ORDER BY CAST(room_num AS UNSIGNED INTEGER);

Casting "Office" as an integer appears to yield a value of 0, which
makes it sort earliest in the list.

I tested this with MySQL 5.0.10.

Regards,
Bill K.

[Kreeves]

I would like them to show up as. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,
20, 30, Office.

Sorry if I used the wrong terminology and caused confusion.

[Gordon Burditt]

>I would like them to show up as. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11,

20, 30, Office.

Sorry if I used the wrong terminology and caused confusion.

Now throw into the mix Offal, oFFice, 11Cat, Office21, Office2, Office02,
Office22, and 1Office2. How should those be ordered?

You can force the field to be ordered as a number; one way being
ORDER BY room+0
but this leaves the room numbers beginning with letters in a possibly
random order.

Gordon L. Burditt