From: | Clinton Adams <clinton(at)vote-smart(dot)org> |
---|---|
To: | "Chad Thompson" <chad(at)weblinkservices(dot)com> |
Cc: | "pgsql-novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Distinct On |
Date: | 2002-08-15 18:40:23 |
Message-ID: | 200208151240.23271.clinton@vote-smart.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Chad -
An alternative is to use l.id in an aggregate (MIN comes to mind) along with
GROUP BY l.full_phone.
It might not matter for your query, but DISTINCT ON takes the first row in
each set. Without an ORDER BY, you have no control on which row will be
selected.
/clinton
On Mon August 12 2002 13:35, Chad Thompson graced us with the following -
> The DISTINCT ON clause is not part of the SQL standard and is sometimes
> considered bad style because of the potentially indeterminate nature of its
> results. With judicious use of GROUP BY and subselects in FROM the
> construct can be avoided, but it is very often the most convenient
> alternative.
>
> Here is my statement:
>
> insert into "8_11_list"
> SELECT DISTINCT on (l.full_phone) l.full_phone, l.id
> FROM list_tz l LEFT JOIN CALL_RESULTS cr ON l.full_phone = cr.phonenum
> WHERE l.full_phone Is Not Null AND cr.phonenum Is Null;
>
> is there a better, more standard SQL, way of doing this?
From | Date | Subject | |
---|---|---|---|
Next Message | Warwick Hunter | 2002-08-15 23:27:10 | Re: BLOBs |
Previous Message | Chad Thompson | 2002-08-15 17:41:30 | BLOBs |