List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:July 5 1999 8:34pm
Subject:Re: ORDER BY numerically
View as plain text  
At 3:59 PM -0400 7/5/1999, Aaron Johnson wrote:
>All,
>
>Sorry if this is already posted in the FAQ's or else where.
>
>I had a column that was varchar and I wanted to sort numerically not
>alphanumerically.
>
>i.e. (1,2,4,10 not 1,10,2,4)
>
>I looked in the manual, but didn't see the section covering this, I searched
>the Mailing lists and there again I did not find all of the answer.  So I did
>some research in Deja.com and then came up with this.
>
>SELECT Field1,Field2,Field3,Field4+0 FROM My_Table WHERE BLAH = 1 ORDER BY
>4,Field1;
>
>Based on a reply in the mailing list "use +0 to force a variable to numeric"
>as in Field4+0.
>
>The 4 in the ORDER BY is I am assuming a reference to the 4th field.?

Right.  You could also say:

SELECT Field1,Field2,Field3,Field4+0 AS f FROM My_Table WHERE BLAH = 1 ORDER BY
f,Field1;

>
>Is this an efficient way to sort numerically?

Probably not, or at least not as efficient as if your column was numeric
in the first place.  Why isn't it, by the way?

>Is this standard SQL behavior?

Is SQL standard?

>Is this standard MySQL behavior?

Yes, if you mean does +0 convert to a number and does ORDER BY n refer
to the nth column.

--
Paul DuBois, paul@stripped
Northern League Chronicles: http://www.snake.net/nl/
Thread
Current Datetoxalot5 Jul
  • Re: Current DateMartin Ramsch5 Jul
  • Re: Current DatePaul DuBois5 Jul
  • ORDER BY numericallyAaron Johnson5 Jul
    • Re: ORDER BY numericallyPaul DuBois6 Jul
    • value changed on insertPeter J. Schoenster6 Jul
      • Re: value changed on insertPat Sherrill6 Jul
      • Re: value changed on insertPaul DuBois6 Jul