[RESOLVED] SQL sorting issue

robkir

I have a table with unit numbers like 902-A, 902-B, etc. The user has an option of sorting the units in ascending order or by the unit's name (like Areneas, Barios, etc). There are no issues if the user opts unit's name, but when the user chooses to sort by unit number it returns:

902-C
902-B
902-A

Obviously I want 902-A, 902-B, 902-C

The SQL Order By is

Order By u.unit_number +0 ASC

Suggestions?

thanks,
Robkir

NogDog

What's the purpose of the "+0" in the order by clause?

Bonesnap

I'm not sure what the "+0" is, but if unit_number is a string it should be sorting the way you want it to. You can take a look into casting and seeing if that helps.

NogDog

Yeah, I was wondering if it was adding zero, and thus forcing an implied cast of the string to an integer, which would leave the "-A" part out of the sort.

NogDog

Looks like the +0 might be an Oracle thing (of perhaps dubious value now?): http://stackoverflow.com/questions/3196611/what-does-0-mean-after-an-order-by-in-oracle

sneakyimp

I would imagine the +0 might be a way to turn unit 0001 into just 1. It's important to realize a couple of things:
1) sorting by unit_number might be surprising because "10-A" could come before "2-A" because the sort would be treating unit_number as a string and, alphabetically speaking, 1 comes before 2 despite the fact that 10 is greater than 2.
2) The collation you have chosen for your unit_number column will matter also.

I would take the +0 out and see if that helps.

robkir

The +0 is used to put the number 1 before the number 10. I did try running it without the +0 and it screwed up the sort.

The field "units" is set up a varchar. Bonesnap suggested casting the varchar as an integer. Hate sound ignorant, but how does one do that with a select statement? (the SELLECT is a three way JOIN to boot, but that surely couldn't affect it).

sneakyimp

You have a choice to make. If your unit_numbers are varchar, you should probably sort them as strings. If you convert a string to an int, chances are you will lose the alphabetical information (i.e,. -A, -B, etc.).

To cast SQL values, MySQL offers a variety of casting functions.

If you were sorting in PHP, you could make use of [man]usort[/man] to define your own fancy sorting function.

robkir

I took a closer look at my data. The units' identification (901-A, etc) once assigned are neither changed or added to. The simplest solution to my sorting problem is to add a 'sortBy' field into the Table. That way it will always be in the correct order.

I appreciate the conversation and suggestions.

Cheers,
Robkir

sneakyimp

If the data doesn't change much, that definitely sounds like a viable solution. If you have a small data set and a consistent unit number format (NNN-AAA), you might also use SQL's string-parsing functions to split at the hyphen and do some kind of fancy sort. I don't have the brain power this morning to figure it out.

robkir

If you have a small data set and a consistent unit number format (NNN-AAA), you might also use SQL's string-parsing functions to split at the hyphen and do some kind of fancy sort.

That's an interesting solution, sneakyimp, -- but one that I'll save for future use. The sort column actually helps with some other issues I hadn't thought about.

Thanks,
robkir