order by within sql not 100% correct

Im trying to order by distance but my distances and are output as followed:

   
 2m4f
    2m5.5f
    2m5f
    2m6f
    2m7f
    3m

Open in new window


as you can see the 2m5.5f comes before 2m.5f

is there away i can make it so any value with .5f will come AFTER the "2.5f" like below

 

 
  2m4f
    2m5f
    2m5.5f
    2m6f
    2m7f
    3m

Open in new window


my code for this is

       $sqlhorses = "SELECT distance,Place,Runners FROM  `horsesrp`  WHERE  `Horse` = '".$horse."' order by distance";

and distance is stored as Varchar(50)
runnerjp2005Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
Not without changing how distance is stored.

One way is to store distance always with a decimal point i.e.

2m4ft => 2m4.0ft
3m => 3m0.0ft

Failing that you can always try this
SELECT * FROM `horsesrp` ORDER BY REPLACE(REPLACE(`distance`,'f',''),'m','');

Open in new window

The query removes the m and f from the result which results in a natural numerical ordering.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave BaldwinFixer of ProblemsCommented:
The values are being stored as strings and that is the correct alphanumeric order for those strings.  If you want them to be ordered as numbers, you have to store them as numbers without any letters.
0
ChloesDadCommented:
It is correct as the values are strings not numbers. In string sorting 'f' comes before '.'

I would always store distances as numbers (2m4f = 2.5 etc) and then use string formatting to display it in miles and furlongs as required

edit - didn't see Dave's post as was typing mine.
0
Ray PaseurCommented:
Why not store data like this 2m4f in two separate columns of type=DECIMAL?  You would have miles=2 and furlongs=4.  Then instead of ORDER BY distance, you would ORDER BY miles, furlongs.
0
Ray PaseurCommented:
Also, we should note that neither SQL nor PHP has native support for furlong arithmetic, so it might be worth converting the information to a standard distance, perhaps in feet, storing the standard distance, then converting the distances back for your desired display format.  This will likely make more sense when the next programmer (or you, in a couple of weeks) sees the code and data.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.