distinct() vs distinct on ()

From: "Haywood J'Bleauxmie" <hj1(at)fc4(dot)outerscape(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: distinct() vs distinct on ()
Date: 2001-11-29 03:14:43
Message-ID: fc.009863ef00009a49009863ef00009a49.9a4a@fc4.outerscape.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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?

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2001-11-29 03:53:30 Re: PL/pgSQL loops?
Previous Message Johnny Jrgensen 2001-11-29 02:05:14 Re: PL/pgSQL loops?