Jeremy Rusnak wrote:
>
> Hi all,
>
> Could anyone tell me how I might be able to force an ORDER BY to
> sort numerically instead of alphanumerically?
>
> Here's an idea of what I've got happening now...
>
> I've got a char column in a table that is used to store invoice
> numbers using invoice number and the state code, eg
> "1012-CA", "1013-CA", "900-PA". This column is "invoice".
>
> In order to generate a list of invoices for output in order
> numerically (say for a shipping manager) I tried something
> like this:
>
> SELECT substring_index(invoice,'-',1) as invoice_number from
> table order by invoice asc;
>
> I get results like this:
>
> 1012-CA
> 1013-CA
> 900-PA
>
> I think it's because invoice_number is being treated as a
> string, so it's sorting like it would a list of names instead
> of a list of numbers.
>
> What's the easiest way to force the type to be numeric? I looked
> in the docs for, say, an INT function...
>
Mysql tends to use +0 to force a variable to numeric. So here's a
'simple' method:
select *, invoice+0 as sort from table order by sort DESC;
If you could have numbers like 9-aaa, 9-acb, ect. then you'd need to
split the string and use the right side to suborder.
jim...
-----------------------------------------------------------
Send a mail to [EMAIL PROTECTED] with
unsubscribe mysql [EMAIL PROTECTED]
in the body of the message to unsubscribe from this list.
