Re: SQL question....

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)

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