List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 12 2002 12:28am
Subject:RE: ORDER BY an ABSolute value
View as plain text  
At 10:08 +1000 2/12/02, David Mackay wrote:
>Thanks for your quick response Dave,
>Have tried this, but no bannana...
>I get:
>"You have an error in your SQL syntax near 'abs(id)' at line 1"

If you can't put an expression in your ORDER BY, that means your version of
MySQL is older than 3.23.  The workaround is to add another column
to the selection list and refer to it in the ORDER BY using an alias or
a column position:

SELECT id, ABS(id) AS absid FROM tbl_name ORDER BY absid;

or

SELECT id, ABS(id) FROM tbl_name ORDER BY 2;

Then ignore the second column of output when processing the result.

>
>Seems a not-valid thing to do these days...
>Is there a work around?
>
>
>Dave
>from
>Oz
>
>
>
>>  select id from table_name order by abs(id);
>>
>>  Dave
>>
>>
>>  Dundee!
>>
>>
>>  On Tue, Feb 12, 2002 at 09:46:25AM +1000, David Mackay wrote:
>>  > G'Day folks,
>>  >
>>  >
>>  > New to PHP/MySQL.
>>  >
>>  > Want to order the results of a SELECT by their 'absolute'
>>  value, not their
>>  > sign.
>>  > So regardless of whether it's +37 or -37, they are both 37
>>  and thus both
>>  > come between 38 & 36, regardless of whether they're + or -.
>>  > eg.
>>  > 100
>>  > -99
>>  > -92
>>  > 91
>>  > 72
>>  > -38
>>  > 37
>>  > -37
>>  > etc...
>>  >
>>  > I have the order DESC but it puts 100 first, and -100 last,
>>  > (that's what you'd usually want, but
>>  > I need the extremes grouped at one end,
>>  > down to the middle point zero......).
>>  >
>>  > In my fantasy world I could do a  ORDER BY ABS(number) DESC
>>  > If there was any other way to achieve the same result that
>>  would be just
>>  > dandy...
>>  >
>>  > I bet this is obvious.
>>  >
>>  >
>>  > Thanks for your time,
>>  >
>>  > David Mackay
>>  >
>>  >
>>  ---------------------------------------------------------------------
>>  > Before posting, please check:
>>  >    http://www.mysql.com/manual.php   (the manual)
>>  >    http://lists.mysql.com/           (the list archive)
>>  >
>>  > To request this thread, e-mail <mysql-thread99181@stripped>
>>  > To unsubscribe, e-mail
>>  <mysql-unsubscribe-turner=tellme.com@stripped>
>>  > Trouble unsubscribing? Try:
>http://lists.mysql.com/php/unsubscribe.php
>
>---------------------------------------------------------------------
>Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
>
>To request this thread, e-mail <mysql-thread99183@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-paul=snake.net@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Thread
ORDER BY an ABSolute valueDavid Mackay12 Feb
  • Re: ORDER BY an ABSolute valueDavid Turner12 Feb
RE: ORDER BY an ABSolute valueDavid Mackay12 Feb
  • Re: ORDER BY an ABSolute valueDavid Turner12 Feb
  • RE: ORDER BY an ABSolute valuePaul DuBois12 Feb
  • RE: ORDER BY an ABSolute valueKeith A. Calaman12 Feb
RE: ORDER BY an ABSolute valueDavid Mackay12 Feb
RE: ORDER BY an ABSolute valueMatthew Walker12 Feb
  • RE: ORDER BY an ABSolute value[AFQ]T1T4N12 Feb
  • RE: ORDER BY an ABSolute value[AFQ]T1T4N12 Feb
  • mysql_fetch_array[AFQ]T1T4N12 Feb
    • Re: mysql_fetch_arrayMichael Stassen12 Feb
      • RE: mysql_fetch_array[AFQ]T1T4N12 Feb
    • Re: mysql_fetch_arrayGeorg Richter12 Feb
  • mysql_fetch_array[AFQ]T1T4N12 Feb