On Wed, May 21, 2008 at 8:33 AM, Karl Denninger <[EMAIL PROTECTED]> wrote:
> Gurjeet Singh wrote: > > On Wed, May 21, 2008 at 4:47 AM, Karl Denninger <[EMAIL PROTECTED]> > wrote: > >> Gurjeet Singh wrote: >> >>> On Tue, May 20, 2008 at 11:44 PM, Karl Denninger <[EMAIL PROTECTED]<mailto: >>> [EMAIL PROTECTED]>> 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. -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
