Re: SQL syntax I've been unable to figure out.... - Mailing list pgsql-sql

2008/12/25 Karl Denninger <karl@denninger.net>:
> Assuming a table containing:
>
> name text
> address text
> uri text
>
> I wish to run a query that will return those rows where:
>
> ("name" is not null) AND (distinct) (uri is the same for two or more entries
> AND name is different between the two entries))
>
> Example data:
>
> george who 1
> sam where 2
> sam what 2
> [null] why 2
> leroy never 2
>
> Returns:
>
> sam 2
> leroy 2

CREATE TABLE TT (NAME TEXT, ADDRESS TEXT, URI TEXT) ;
INSERT  INTO TT VALUES   ('george', 'who', 1) , ('sam', 'where', 2) , ('sam', 'what', 2) , (NULL, 'why', 2) , ('leroy',
'never',2) ;
 

SELECT TT.NAME, TT.URI  FROM (SELECT URI FROM TT WHERE NAME IS NOT NULL GROUP BY URI HAVING SUM(1) > 1 ) A
JOIN TT ON A.URI = TT.URI AND TT.NAME IS NOT NULL
GROUP BY TT.NAME, TT.URI;