order by last digits

Forum Moderators: open

Message Too Old, No Replies

order by last digits

order by, sql, mysql

belfasttim

9:58 pm on Aug 3, 2009 (gmt 0)

Hi--

I hope this is an easy one, I don't quite know how to phrase the search right so I haven't found anything on the net--

I have a column called product_id-- it consists of a long number identifying the product, a dash, and a short number identifying the series (eg. 123456789-1 through 123456789-50).

I want to pull all products from this table, and order by that series number-- so instead of getting

123456789-1
123456789-10
123456789-11
123456789-12
...
123456789-19
123456789-2

I'd like to get

123456789-1
123456789-2
123456789-3
123456789-4
123456789-5
. . .

This is how the data is provided by the supplier, so I can't really rewrite these identifiers-- and since I don't always get them in order from the supplier, I can't just write them with a sequential ID column and order by that.

Any ideas?

Thanks!

dreamcatcher

6:47 am on Aug 4, 2009 (gmt 0)

ORDER BY SUBSTRING(11,2)*100

Try that.

Or if the first part varies in length:

ORDER BY SUBSTRING(product_id,LOCATE(product_id,'-')+1,2)*100

belfasttim

3:22 pm on Aug 4, 2009 (gmt 0)

thanks DC-- the substring trick worked, but I ended up writing the substring to a separate column to order by, and I'll do a split and insert when I load new records so this won't happen again.

Thanks for the help!

dreamcatcher

8:03 pm on Aug 4, 2009 (gmt 0)

No problem, glad to help.