> -----Original Message----- > From: sunpeng [mailto:bluevaley@xxxxxxxxx] > Sent: Thursday, October 14, 2010 7:34 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: how to write an optimized sql with two same subsql? > > 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? > Is that what you want: WITH gr_counts AS ( SELECT groupid, COUNT(*) AS CNT FROM A GROUP BY groupid) SELECT C.groupid AS groupid1, D.groupid AS groupid2 FROM gr_counts C, gr_counts D WHERE D.groupid > C.groupid AND D.count > C.count; This will execute: SELECT groupid, COUNT(*) AS CNT FROM A GROUP BY groupid only once. Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
