distinct() vs distinct on ()

Поиск
Список
Период
Сортировка
От Haywood J'Bleauxmie
Тема distinct() vs distinct on ()
Дата
Msg-id fc.009863ef00009a49009863ef00009a49.9a4a@fc4.outerscape.net
обсуждение исходный текст
Ответы Re: distinct() vs distinct on ()  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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?



В списке pgsql-sql по дате отправления:

Предыдущее
От: "Johnny J\xF8rgensen"
Дата:
Сообщение: Re: PL/pgSQL loops?
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: PL/pgSQL loops?