Re: how to write an optimized sql with two same subsql? - Mailing list pgsql-general

On 10/14/2010 05:34 PM, sunpeng wrote:
> We have a table A:
> CREATE TABLE A(
>    uid integer,
>    groupid integer
> )
> Now we use this subsql to get each group's count:
> SELECT count(*) as count
> FROM A
> GROUP BY groupid
> ORDER BY groupid
>
> Then we try to find the group pair with following conditions:
> SELECT c.groupid as groupid1,d.groupid as groupid2
> FROM subsql as c, subsql as d
> WHERE d.groupid > c.groupid
>               and d.count > c.count;
>
> Does that mean subsql will be executed twice? or how to write the
> optimized sql?
>
>
What are you trying to discover about groups and their frequency in
tablea?  Does the numberical value of groupid have any meaning in your
system?