Re: How do i sort by

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 18 Sep 2006 13:14:07 -0700
Message-ID: <1158610447.618624.327130@d34g2000cwd.googlegroups.com>

tawright915 wrote:
> fitzjarrell_at_cox.net wrote:
> > tawright915 wrote:
> > > I'm trying to pull all records that have mulitpule addresses.
> > >
> > > One account can have more that one address. How would i sort on this?
> > > i tried the lag key word, but I'm not sure this is working for me.
> > >
> > > Thanks
> > > Tom
> >
> > And your current query is?
> >
> >
> > David Fitzjarrell
>
> Good Idea...sorry here it is.
>
> select ky_ba, ky_cust_no, ky_prem_no from bill_acct a
> where (select count(*) from bill_acct b
> where a.ky_cust_no = b.ky_cust_no
> and a.cd_co = '17'
> and a.cd_ba_stat = '02'
> and a.cd_res_comm = 'R') >= 2
> and rownum > 50;

This query doesn't return ANY rows, right? :) ROWNUM > 50 does this to you. Try this:

select ky_ba, ky_cust_no, ky_prem_no from bill_acct where ky_cust_no IN (
  SELECT ky_cust_no
    FROM bill_acct

   WHERE cd_co = '17'
     and cd_ba_stat = '02'
     and cd_res_comm = 'R'

   GROUP BY ky_cust_no HAVING COUNT(*) > 1)

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Mon Sep 18 2006 - 15:14:07 CDT