Re: SQL question....

Gurjeet Singh wrote:
> On Wed, May 21, 2008 at 8:33 AM, Karl Denninger <karl(at)denninger(dot)net
> <mailto:karl(at)denninger(dot)net>> wrote:
>
> Gurjeet Singh wrote:
>> On Wed, May 21, 2008 at 4:47 AM, Karl Denninger
>> <karl(at)denninger(dot)net <mailto: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>
>> <mailto: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,
>>
>>
>>
> I used an "order by" and also increased the count to "> 2" because
> there are a lot of blank "name" records in there as well (but I
> don't want to select on those; as an artifact of how the system
> works there will usually be a blank name entry for most IP
> corresponding entries, but not all)
>
>
> You can add a filter to the subquery using
>
> WHERE name <> ''
>
> Also, if you don't have it already, you may create an index on IP
> column for better performance.
> Mail sent from my BlackLaptop device
Its a very large table and is indexed already...

Karl Denninger (karl(at)denninger(dot)net)
http://www.denninger.net