Re: Query seemingly ignoring "order by" after upgrade

You may have encountered a bug; one thing you could do to avoid having to downgrade is specify the column number you wish to sort on (4th column in your case), as in:

ORDER BY 4 DESC LIMIT 10; Dan

On 6/22/07, Andrew Carlson <naclosagc@gmail.com> wrote:
>
> I upgraded to Mysql 5.0.42 from 4.1.14 yesterday. Afterwards, a query the
> I
> run every day to capture data on nodes that have increased backup load
> the
> most in TSM, seems to be ignoring my "order by" statement. Here is the
> query
> and the output:
>
> mysql> select occupancy.node_name as NodeName,nodetrend.occupancy/1024 as
> "Occupancy on 09/24/2006 (GB)",sum(occupancy.physical_mb)/1024 as
> "Occupancy
> Today (GB)",(sum(occupancy.physical_mb) - nodetrend.occupancy)/1024 as
> "Occupancy Increase (GB)" from nodetrend,occupancy where
> nodetrend.date="2006-09-24"
> and occupancy.stgpool_name='BACKUPPOOL' and
> nodetrend.node_name=occupancy.node_name group by occupancy.node_name order
> by "Occupancy Increase (GB)" desc limit 10;
>
> +-------------+------------------------------+----------------------+-------------------------+
> | NodeName | Occupancy on 09/24/2006 (GB) | Occupancy Today (GB) |
> Occupancy Increase (GB) |
>
> +-------------+------------------------------+----------------------+-------------------------+
> | AMHFP01 | 213.378672 | 188.096826|
> -
> 25.281846 |
> | AMHFP01-S | 15.284570 | 25.520684|
> 10.236113 |
> | AMHROCP01 | 8.740791 | 15.711035|
> 6.970244 |
> | ANDYC | 2.503848 | 2.503848|
> 0.000000 |
> | BHSCAFAS01 | 7.057617 | 11.109980|
> 4.052363 |
> | BJC11101828 | 5.401748 | 3.433613|
> -
> 1.968135 |
> | BJC11113758 | 15.911094 | 15.911094|
> 0.000000 |
> | BJCAMSQL01 | 72.650596 | 102.609326|
> 29.958730 |
> | BJCAMWEB01 | 3.703057 | 8.468467|
> 4.765410 |
> | BJCBCA01 | 22.580410 | 33.760918|
> 11.180508 |
>
> +-------------+------------------------------+----------------------+-------------------------+
> 10 rows in set (0.24 sec)
>
> Any suggestions? I can try backing off to 5.0.41 if anyone thinks that
> would
> help.
>
> Thanks
>
> --
> Andy Carlson
>
> ---------------------------------------------------------------------------
> Gamecube:$150,PSO:$50,Broadband Adapter: $35, Hunters License:
> $8.95/month,
> The feeling of seeing the red box with the item you want in it:Priceless.
>
Received on Fri Jun 22 10:07:31 2007