Re: SQL question....

On Wed, May 21, 2008 at 8:33 AM, Karl Denninger <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>
> 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,
>
>
>
> 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.

--
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