Re: SQL question....

[Karl Denninger <karl(at)denninger(dot)net>]

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