From: | "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> |
---|---|
To: | "Karl Denninger" <karl(at)denninger(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: SQL question.... |
Date: | 2008-05-21 07:52:39 |
Message-ID: | 65937bea0805210052l192cd154k4e6be8014db2e4d0@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Karl Denninger | 2008-05-21 11:39:11 | Re: SQL question.... |
Previous Message | Karl Denninger | 2008-05-21 03:03:58 | Re: SQL question.... |