Received: from xserver.server (barco2.comcasttel.net [206.135.253.162]) by hub.org (8.10.1/8.10.1) with ESMTP id e6SGINB10718 for ; Fri, 28 Jul 2000 12:18:23 -0400 (EDT) Received: by XSERVER with Internet Mail Service (5.5.2650.21) id ; Fri, 28 Jul 2000 12:19:18 -0400 Message-ID: From: Henry Lafleur To: pgsql-sql@fc.emc.com.ph, Henry Lafleur , pgsql-sql@postgresql.org Subject: RE: RE: Re(2): optimize sql Date: Fri, 28 Jul 2000 12:18:08 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: text/plain; charset="iso-8859-1" X-Archive-Number: 200007/274 I'm kind of new to pgsql, but as long as MAX works for boolean fields, they you can just change the first query below with: HAVING MAX(active) != 't' but it seems that pgsql can have user defined aggregates, so you could define a function that computes the MAX of a boolean and define true to be the max. Henry -----Original Message----- From: pgsql-sql@fc.emc.com.ph [mailto:pgsql-sql@fc.emc.com.ph] Sent: Thursday, July 27, 2000 9:28 PM To: HLafleur@phoenixforge.com; pgsql-sql@postgresql.org Subject: Re: RE: Re(2): [SQL] optimize sql but active is a boolean field. HLafleur@phoenixforge.com writes: >If you know that 't' will always be the highest character in the active >field for all records: > >SELECT name FROM office, office_application >WHERE code = office_code >GROUP BY name >HAVING MAX(active) < 't' > >Of course, if you have an active that is 'z' for example, then this won't >work. I think this should work also regardless of max(active) for the >table: > >SELECT name FROM office, office_application >WHERE code = office_code AND active <= 't' >GROUP BY name >HAVING MAX(active) < 't' >UNION >SELECT name FROM office, office_application >WHERE code = office_code AND active >= 't' >GROUP BY name >HAVING MIN(active) > 't' > >Henry > > >-----Original Message----- >From: pgsql-sql@fc.emc.com.ph [mailto:pgsql-sql@fc.emc.com.ph] >Sent: Wednesday, July 26, 2000 9:40 PM >To: pgsql-sql@postgresql.org; reedstrm@rice.edu >Subject: Re(2): [SQL] optimize sql > > >reedstrm@rice.edu writes: >>How does the output of the above differ from: >> >>SELECT name FROM office, office_application >>WHERE code = office_code >>AND active != 't'; >> >>Without knowing the table structures (which tables to active, code, >>and office_code belong to?) it's hard to suggest much else. >> >>Ross > > >The name and code fields belong to office table. While >office_code and active fields belong to office_application table. >The name field have duplicates and among the duplicates, >only one active field is TRUE. I just wanted to get name field >that has no TRUE active field. Any other idea? Thanks. > >sherwin