Re: Natural order of things.

On 27/04/06, tedd <tedd@sperling.com> wrote: > >On Wed, 2006-04-26 at 16:57, Robert Cummings wrote: > >> On Wed, 2006-04-26 at 16:51, tedd wrote: > >> > Hi gang: > >> > > >> > I posted the following question to the MySQL list, but the only > >> > answer I received thus far was a php solution (it didn't work for > >> > what I wanted). > >> > > >> > As such, maybe if I post a MySQL question to the PHP group, then I'll > >> > receive a MySQL answer -- so here goes: > >> > > >> > I'm using the following query, and it works. > >> > > >> > SELECT id, title, url_image_small > >> > FROM $dbtable > >> > WHERE type="type_title" > >> > ORDER BY title > >> > LIMIT $offset, $rowsPerPage" > > > > > >-snip- > > > You need ot perform a type conversion from string to integer. > > > >Sorry, just realized... "Basel Square" is part of the entry in the table > >:/ Do you have entries that aren't prefixed with Basel Square? > > > >Cheers, > >Rob. > > Rob: > > Yes, there are other titles, such as: > > Celtic Deco 1 > Lucerne 1 > Dutch Hearth 1 > Yeah, two items of information in the same column's not really a good idea, which is why you're finding it so tricky. So what you can do is this: SELECT title, LEFT(title, LENGTH(title) - LENGTH(SUBSTRING_INDEX(title, ' ', -1))) AS name, CAST(SUBSTRING_INDEX(title, ' ', -1) AS UNSIGNED) AS number FROM $dbtable ORDER BY name, number; So that's finding the position of the last space and putting everything up to that in name. Then it's taking everything after that, turning it into an integer and putting it in number. Then the ORDER BY works as you'd like because it's sorting on an actual number rather than a string representation of a number. If you'd had the name and the number in separate columns in the first place it would be so much simpler. -robin