Help on Sort Query

[Riparian]

I have a stock dbase with stock numbers like d100-d199, d1000-d1999 etc

 

phpmyadmin (as well as my own slq statements)  sorts them into ....

 

d001 to d100 then lists d1000 to d1020 then d102 d1002 d103 d1003 etc

 

my query is very simple;

 

select * from $data where model_number like '$_POST[Model]%' order by model_number ASC

 

Can anyone point me in the right direction  to get the sort to go through d001 to d999 then d1000 to d1999 etc

 

 

Cheers

[Muddy_Funster]

I'll go ahead and assume that your refference to model_number is the same thing you called stock number at the top of the post.  What you are looking for is a "Natural" Sort - something not inhearently supported in MySQL.  Have a search about on that here in the forums and in google and you should find what you are looking for.

[PFMaBiSmAd]

You have three choices for doing this in your query -

 

1) Alter your saved values by removing the leading 'd' character and then alter the table to make that column an INT data type. You can simply concatenate the 'd' character when you retrieve and display the values. This would result in both the most efficient storage and fastest queries.

 

2) Alter your values by adding leading zero's to the numeric part so that the length of all the values is the same and they can be compared/ordered correctly as strings. d100-d199 would become d0100-d0199.

 

3) Form a slightly more complicated query that gets just the numeric part of the values as a number and uses that in the ORDER BY term. This of course would be the slowest of these three methods.

[Riparian]

Absolutely excellent advice.... thank you !