Skip site navigation (1) Skip section navigation (2)

Re: Distinct On

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 (view raw or flat)
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?


In response to

  • Distinct On at 2002-08-12 19:35:15 from Chad Thompson

pgsql-novice by date

Next:From: Warwick HunterDate: 2002-08-15 23:27:10
Subject: Re: BLOBs
Previous:From: Chad ThompsonDate: 2002-08-15 17:41:30
Subject: BLOBs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group