Re: SQL question....

On Wed, May 21, 2008 at 4:47 AM, Karl Denninger <karl(at)denninger(dot)net> wrote:

> Gurjeet Singh wrote:
>
>> On Tue, May 20, 2008 at 11:44 PM, Karl Denninger <karl(at)denninger(dot)net<mailto:
>> karl(at)denninger(dot)net>> wrote:
>>
>> .... assuming the following schema:
>>
>> create table access (name text, address ip)
>>
>> I want to construct a SELECT statement which will return ONLY
>> tuples containing IP and name pairs IF there is an IP that has two
>> or more NAMEs associated with it.
>>
>> I've not figured out how to do this; I can get a list of all IPs
>> and names ordered by IP, which I could then parse with a different
>> program (e.g. "Select name, address from access order by
>> address"), but the idea of course is to do it with one SELECT
>> statement and return only rows that have multiple names listed for
>> a given IP.
>>
>>
>> try this:
>>
>> select ip, name from access where ip in ( select ip from access group by
>> ip having count(name) > 2);
>>
>> heven't execued it, so may need some coaxing. Let me know the results.
>>
>> Best regards,
>> --
>>
>> A small modification got CLOSE.... I can live with that set of
> results..... I think.

I am glad.

Harold had posted almost identical solution one hour before I did (I had the
mail ready to be sent almost after you posted, but lost power and network
connection for about an hour).

Can you please post your modified query, for the record; we might still be
able to get you _exactly_ what you want.

Best regards,

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device