# Order complex aggregation results in 'calculate' **URL:** \ **Category:** Rails\ **Created:** [May 27, 2008, 4:43pm UTC](https://www.ruby-forum.com/t/order-complex-aggregation-results-in-calculate/138770 "2008-05-27T16:43:28Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![duco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/b782af/32.png) [@duco](https://www.ruby-forum.com/u/duco)\ **Post date:** [May 27, 2008, 4:43pm UTC](https://www.ruby-forum.com/t/order-complex-aggregation-results-in-calculate/138770/1 "2008-05-27T16:43:28Z")
Hi, How would you order the results of a complex aggregation using ‘calculate’ (from ActiveRecord::Calculations::ClassMethod)? I have a “legacy” table from which I would like to retrieve some aggregated data, with an ordering. Something like: Entry.calculate(‘avg’, ‘thevalue’, { :order=\> ‘avg\_thevalue DESC’ }) which generates the following (Postgres) query: SELECT avg(subject) AS avg\_subject FROM logevents ORDER BY avg\_subject This works, but the :order option depends on what I think are implementation details of the function calculate: namely the generation of the “AS avg\_subject” alias in the query. This becomes painful if I need more complex SQL expressions to get to the data (remember this is a legacy table :-)): Entry.calculate(‘avg’, ‘CAST(SUBSTRING(thevalue, ‘[0-9]+.[0-9]+’) as double precision)’ ) This generates: SELECT avg(CAST(SUBSTRING(subject, ‘[0-9]+.[0-9]+’) as double precision)) AS avg\_cast\_substring\_subject\_0\_9\_0\_9\_as\_double\_precision FROM logevents to sort the results, I would need to pass { :order =\> ‘avg\_cast\_substring\_subject\_0\_9\_0\_9\_as\_double\_precision DESC’ } as an option to ‘calculate’. Something tells me that this is _not_ a good idea. The exact name of the alias seems database adapter dependent and it just looks ugly and fragile. It seems that this solution uses knowledge of implementation details, which is of course never a good idea, but I can’t think of an alternative (besides sorting the results in Ruby instead of SQL). Greetings, Duco ---
**Author:** ![duco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/b782af/32.png) [@duco](https://www.ruby-forum.com/u/duco)\ **Post date:** [May 27, 2008, 4:49pm UTC](https://www.ruby-forum.com/t/order-complex-aggregation-results-in-calculate/138770/2 "2008-05-27T16:49:23Z")
On 27 May 2008, at 15:38, duco wrote: > the alias seems database adapter dependent and it just looks ugly and > fragile. It seems that this solution uses knowledge of implementation > details, which is of course never a good idea, but I can’t think of an > alternative (besides sorting the results in Ruby instead of SQL). 2 ideas: 1 - use column\_alias\_for to generate those column names (again, relying on implementation, but a little less so) 2 - no idea how portable sql wise this is, but in mysql at least you can say ‘order by 2’ to order by the second column ---
**Author:** ![duco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/b782af/32.png) [@duco](https://www.ruby-forum.com/u/duco)\ **Post date:** [May 27, 2008, 5:05pm UTC](https://www.ruby-forum.com/t/order-complex-aggregation-results-in-calculate/138770/3 "2008-05-27T17:05:59Z")
On May 27, 4:48 pm, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: … > 2 ideas: > 1 - use column\_alias\_for to generate those column names (again, > relying on implementation, but a little less so) > 2 - no idea how portable sql wise this is, but in mysql at least you > can say ‘order by 2’ to order by the second column > > > Greetings, > > > Duco 1. is a nice idea,thanks! I checked it and indeed it is specified in the SQL92 standard, so it seems portable enough. I also thought of 1, but the problem is ‘column\_alias\_for’ is a private method. Duco