ORDER BY question

This may be a fairly dumb question, but it’s got me confused.

I have a client who has product names with numbers in front of them, something like this…

currently looks like:
321 ABC
234 DEF
123 ABC
123 DEF

they are asking me to order them by the alpha portion of the title and then order by the numbers. So the alphas are basically ‘grouped’ together in the list and the numbers are high to low within each alpha group. Is this possible with an ORDER BY statement? (both numbers and text is in one field)

client wants it to looks like:
321 ABC
123 ABC
234 DEF
123 DEF

In hindsight I should have split up the model number and letter fields, but since this request was not made/known at the beginning of the project I thought I was good to go.

I’m open to any ideas/thoughts/suggestions. Thanks in advance for any guidance.

actually, yes you can

however, it has to be the leftmost part of the column, and it works only in mysql (which is nice, because this is the mysql forum)

[indent]quote from da manual:

For CHAR and VARCHAR columns, indexes can be created that use only part of a column, using col_name(length) syntax to index a prefix consisting of the first length characters of each column value. BLOB and TEXT columns also can be indexed, but a prefix length must be given.

The statement shown here creates an index using the first 10 characters of the name column:

CREATE INDEX part_of_name ON customer (name(10));[/indent]

Thanks for all the help guys, for simplicities sake I just went ahead and split the fields. It only took a couple of hrs and now I can sort however, whenever and whatever the client wants without loosing any hair :slight_smile:

yup :slight_smile:

    BY SUBSTRING(title FROM 5)
     , SUBSTRING(title FROM 1 FOR 3) DESC

Look at using SUBSTRING_INDEX and split on the space between the numbers and alpha characters.

Ok, what if I wanted to ignore the numbers altogether? And just sort by the alpha characters? I have found another number field (a product spec that mimics the number) I can key off of.

Rudy, your example would work, except some numbers are 2 digits, some are 4, sorry I didn’t show that in my example.

So… is there a way to Alpha sort this list? Ignore the numerics?

3231 ABC
23 DEF
123 ABC
1223 GHI

With it all in one text field it is going to be extremely inefficient since it will have to read the entire table to get the data to sort since you can’t have indexes on parts of fields.

The best option would be to refefine the table to split that content into separate fields.

Although splitting the column will take longer initially it will quickly make up for the time spent in the saving of time each time the query is run.