pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
distinct() vs distinct on ()
2+ messages / 2 participants
[nested] [flat]

* distinct() vs distinct on ()
@ 2001-11-29 03:14  Haywood J'Bleauxmie <hj1@fc4.outerscape.net>
  0 siblings, 1 reply; 2+ messages in thread

From: Haywood J'Bleauxmie @ 2001-11-29 03:14 UTC (permalink / raw)
  To: 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?




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: distinct() vs distinct on ()
@ 2001-11-29 05:02  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Haywood J'Bleauxmie <hj1@fc4.outerscape.net>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2001-11-29 05:02 UTC (permalink / raw)
  To: Haywood J'Bleauxmie <hj1@fc4.outerscape.net>; +Cc: pgsql-sql

"Haywood J'Bleauxmie" <hj1@fc4.outerscape.net> writes:
> The first SELECT does NOT work in that the distinct() is ignored.

Hard to believe.  Would you mind providing a *complete* example?
Queries with no schema or test data are not very helpful.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-11-29 05:02 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-29 03:14 distinct() vs distinct on () Haywood J'Bleauxmie <hj1@fc4.outerscape.net>
2001-11-29 05:02 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox