List:General Discussion« Previous MessageNext Message »
From:Luc Foisy Date:July 9 2002 8:33pm
Subject:RE: Basic Q's: Numerical Sorting
View as plain text  
Here is the solution I posted about t months ago. I would also add, this solution can
handle both text and numbers in the same field...:

my boss figured out a nice solution to this

ORDER BY IF(ABS(Item) > 0,LPAD(Item,9,'0'),Item)

This will pad numbers ( ok it might not do so hot with DECIMAL, but it can be fixed to
work I supposed) with 0

So it should sort 

000000001
000000002
000000010
000000022
000000050
000000230

instead of

1
10
2
22
230
50

And it will skip padding text cause I guess the ABS of text is 0, and the "numbers" and
words will appear separated from each other

My boss is smart :)

> -----Original Message-----
> From: CVIOG at UGA [mailto:cviog@stripped]
> Sent: Tuesday, July 09, 2002 4:22 PM
> To: mysql@stripped
> Subject: Basic Q's: Numerical Sorting
> 
> 
> I have a fairly basic question: How do I sort
> numerically?  Normally when I query using "ORDER BY
> (field)", it orders by the first digit (i.e. 1, 10, 2,
> 21, 3, 32) rather than by number (1, 2, 3, 10, 21,
> 32).
> 
> Thanks for any help
> Dave
> 
Thread
Basic Q's: Numerical SortingCVIOG at UGA9 Jul
  • Re: Basic Q's: Numerical SortingSerge Paquin9 Jul
  • Re: Basic Q's: Numerical SortingDan Nelson9 Jul
RE: Basic Q's: Numerical SortingLuc Foisy9 Jul