Обсуждение: Distinct On

Поиск
Список
Период
Сортировка

Distinct On

От
"Chad Thompson"
Дата:
What is the word on "distinct on".
 
It is extremly useful for getting more that one column and still taking advantage of the distinct function, but the docs say:
 
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?
 
Thanks
Chad

Re: Distinct On

От
Clinton Adams
Дата:
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?