Given: SELECT t1.t1_f1, max(t2.t2_f1) FROM t1 JOIN t2 on t1.id = t2.t1_id GROUP BY t1.t1_f1 ORDER BY max(t2.t2_f1), t1.t1_f1;
If I recall correctly in PostgreSQL you can do: SELECT t1.t1_f1, max(t2.t2_f1) FROM t1 JOIN t2 on t1.id = t2.t1_id GROUP BY 1 ORDER BY 2,1; i.e. you can use the positional arguments in place of the expressions. 2013/9/3 Niphlod <[email protected]> > this doesn't return any other meaningful info about the id of the group, > it just uses an aggregate on it. In that case, you'd still need to orderby > by min(id). > tl;dr : any groupby query can be ordered either by a field included in a > group by or by an aggregate of any other column. > > > On Tuesday, September 3, 2013 7:07:41 PM UTC+2, Derek wrote: >> >> maybe this way: >> select min(id), count(*), surname from table group by surname >> >> ? >> >> On Tuesday, September 3, 2013 5:43:42 AM UTC-7, Johann Spies wrote: >>> >>> On 3 September 2013 11:42, Bassem Bouguerra <[email protected]> wrote: >>> >>>> I am sorry. I am trying to groupby and not orderby. >>>> I want to select from 2 tables and groupby the id of one table. This >>>> works fine in sqllight but throws an error on postgresql. >>>> >>>> You cannot do that in Postgresql. If you want to group by in Postresql >>> you have to use all the fields in the groupby part that was used in the >>> 'select' part of the query. >>> >>> Regards >>> Johann >>> >> -- > > --- > You received this message because you are subscribed to the Google Groups > "web2py-users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
