Re: SQL question.... - Mailing list pgsql-sql

Gurjeet Singh wrote:
> On Wed, May 21, 2008 at 8:33 AM, Karl Denninger <karl@denninger.net 
> <mailto:karl@denninger.net>> wrote:
>
>     Gurjeet Singh wrote:
>>     On Wed, May 21, 2008 at 4:47 AM, Karl Denninger
>>     <karl@denninger.net <mailto:karl@denninger.net>> wrote:
>>
>>         Gurjeet Singh wrote:
>>
>>             On Tue, May 20, 2008 at 11:44 PM, Karl Denninger
>>             <karl@denninger.net <mailto:karl@denninger.net>
>>             <mailto:karl@denninger.net <mailto:karl@denninger.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@denninger.net)
http://www.denninger.net