[SQL] SQL syntax I've been unable to figure out....

2008-12-25 Thread Karl Denninger 

for the following

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

I haven't been able to concoct an SQL query that will filter out the 
"double entry" for sam (I only want one of them) but will return those 
cases where more than one name has a given URI in the table.


I can do it programmatically (e.g. from "C" selecting the table ordered 
by URIs, distinct by name) and then stepping through by hand, but can't 
figure out a single SQL statement that will accomplish this.


Thanks in advance

--
--
Karl Denninger
[email protected]


begin:vcard
fn:Karl Denninger
n:Denninger;Karl
email;internet:[email protected]
x-mozilla-html:TRUE
version:2.1
end:vcard


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] SQL syntax I've been unable to figure out....

2008-12-25 Thread Rodrigo E. De León Plicet 
2008/12/25 Karl Denninger :
> 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;

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql