List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:March 20 2009 10:57pm
Subject:Re: MySQL General Discussion question
View as plain text  
Hi Rich,
I believe that since they are definitely considered as strings they are 
sorted as such,
how can you pretend that '#2 NOV' is considered as a number?
If you cannot define a rule in the structure of the data it is 
impossible to sort,  not only in sql, but in life!
IF we take for granted that values start from first or second position 
('#') you can use something like this:

SELECT setname from sets order by CASE SUBSTRING(setname,1,1) WHEN '#' THEN
SUBSTRING(setname,2) ELSE setname END;


Cheers
Claudio


Richard Gagnon wrote:
> Sorting a varchar field alphabetically with correct numerical order help
> needed
>
>  
>
> I have  a varchar 50 field that contains product names, which are typically
> numerical, alphabetical and punctuation thrown in. I would like to have them
> returned in some sort of order that is roughly alphabetical, but with the
> numbers in numerical order. The basic Order By clause does not do it
> correctly.
>
> An example is:
>
>  
>
> SELECT setname  FROM sets ORDER BY setname
>
>  
>
> Sample values of setname are:
> 658
> #1 JCAL
> 011
> #2 NOV
> #11 NOV
> #12 NOV
> 985
>
> ABC
>
> #123 NOV
>
> The results I get are:
> #1 JCAL
> #11 NOV
> #12 NOV
>
> #123 NOV
> #2 NOV       <<<<<<<<< wrong
> 011
> 658
> 985
>
> ABC
>
>  
>
> The results I want are:
>
> #1 JCAL
> #2 NOV     <<<<<<<<< should be here
> #11 NOV
> #12 NOV
>
> #123 NOV
> 011
> 658
> 985
>
> ABC
>
>
> In the above, the #2 JCAL should be second, otherwise, list is correct. I
> could also live with the values beginning with # or any alpha character
> coming after the numerical ones, but the 1, 11, 2, order is the issue.
>
>  
>
> Does anyone have any idea how to do this? I have been playing around with
> various suggested ways, including casting and converting, but so far have
> not been able to solve this. Any ideas would be greatly appreciated.
>
>  
>
> Thanks, Rich
>
>  
>
>
>   

Thread
MySQL General Discussion questionRichard Gagnon20 Mar
  • Re: MySQL General Discussion questionClaudio Nanni20 Mar
    • Re: MySQL General Discussion questionmichael21 Mar
  • Re: MySQL General Discussion questionJoerg Bruehe21 Mar