Re: Ordering of records in group by not possible

[Chris Kratz <chris(dot)kratz(at)vistashare(dot)com>]

On Wednesday 26 April 2006 11:19 am, Ludwig Isaac Lim wrote:
> Hi:
> > You could order by "column 2" if you want to order on the
> > results on
> > your aggregate:
> > 	Select a, aggregate(b)
> > 	from c
> > 	group by a
> > 	order by a,2
>
> another alternative is :
>
>    select a , aggregrate(b) as <alias>
>    from c
>    group by a
>    order by a, <alias>
>
> e.g.
>
>    select a,sum(b) as sum_b
>    from c
>    group by a
>    order by a, sum_b
>
> ludwig lim
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Ironically, before we realized we weren't getting the results we expected, we 
were doing something like this.

select a,aggregate(b) as b
from c
group by a
order by a,b

We got lucky the first couple of tests and it appeared to work.  It was only 
later that we realized that the sort was actually sorting the aggregated 
values after the group by, not the rows going into the order sensitive 
aggregate as we had hoped.

I actually like the fact that the sort is the last thing that is done on a 
query, in most cases this is what makes sense.  It's just frustrating in this 
one place to have little control of the records going into the grouping code 
since the aggregates we are using are order sensitive.  Think functions like 
first and last.

-Chris
-- 
Chris Kratz