[GENERAL] how to write an optimized sql with two same subsql?

[sunpeng]

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?