Sorting correctly...

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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

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

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.

Re: Sorting correctly... wrote:
Quoted text here. Click to load it

Try something like this:

SELECT room_num FROM myTable

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.

Bill K.

Re: Sorting correctly...

Quoted text here. Click to load it

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

Re: Sorting correctly...

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.

Re: Sorting correctly...

Quoted text here. Click to load it

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

Site Timeline