Re: Distinct On

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?