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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
