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: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Chad -

An alternative is to use 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 


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,
> 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-2017 The PostgreSQL Global Development Group