MySQL Server Forum - ORDER BY problem

Author

ORDER BY problem

hoonew

2005-06-27, 1:23 pm

I have table X with UNSIGNED INT column C. I want to order by C ASC,
but have the 0 value last. So, I thought that this should work:

SELECT *, (C-1) AS o from X ORDER BY o;

but it doesn't. Doing a SELECT on (C-1) shows an extremely large
POSITIVE integer value where C=0, as I expected from an UNSIGNED column.
But it is ordered first (as -1). Is this intended behavior? Am I
doing something wrong? I worked around it for now with:

....ORDER BY C>0 DESC, C ASC;

but my curiosity is aroused... And which is faster, anyway?

Thx

P.S. Originally (accidentally) posted in m.d.mysql-win32, sorry

Bill Karwin

2005-06-27, 8:23 pm

hoonew wrote:
> Doing a SELECT on (C-1) shows an extremely large
> POSITIVE integer value where C=0, as I expected from an UNSIGNED column.
> But it is ordered first (as -1). Is this intended behavior? Am I
> doing something wrong? I worked around it for now with:
>
> ...ORDER BY C>0 DESC, C ASC;

Try ORDER BY CAST(C-1 AS UNSIGNED)

You could also use ORDER BY IF(C = 0, 999999999, C)

> but my curiosity is aroused... And which is faster, anyway?

Why don't you do some timings and tell us which is faster. ;-)

Seriously, many performance issues depend on the distribution of data
values in your database, so the best method of determining which is
fastest is to try it on _your_ database and measure the speed for yourself.

Regards,
Bill K.

Sponsored Links