Received: from fc4.outerscape.net ([207.8.125.118]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAT3G1m15428 for ; Wed, 28 Nov 2001 22:16:01 -0500 (EST) (envelope-from hj1@fc4.outerscape.net) Message-id: Date: Wed, 28 Nov 2001 21:14:43 -0600 Subject: distinct() vs distinct on () To: pgsql-sql@postgresql.org From: "Haywood J'Bleauxmie" MIME-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Archive-Number: 200111/396 X-Sequence-Number: 5778 Three tables: LISTING, BROKER, LISTING_BROKER Each LISTING can have one or more BROKERs so LISTING_BROKER is a link. Should not the following be equivalent? SELECT distinct(l.listing_id) FROM listing l, broker b, listing_broker lb WHERE l.listing_id = lb.listing_id AND b.broker_id = lb.broker_id AND b.company_id = 1; SELECT distinct on (l.listing_id) l.listing_id FROM listing l, broker b, listing_broker lb WHERE l.listing_id = lb.listing_id AND b.broker_id = lb.broker_id AND b.company_id = 1; The first SELECT does NOT work in that the distinct() is ignored. The second SELECT works correctly. Am I missing something?