Re: find position of row in set of rows

From: cate <catebekensail_at_yahoo.com>
Date: Thu, 28 Jan 2010 11:11:07 -0800 (PST)
Message-ID: <e48334c4-6e50-4f75-a2c4-722153098bde_at_l11g2000yqb.googlegroups.com>

On Jan 28, 1:04�pm, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> On Jan 28, 1:49�pm, cate <catebekens..._at_yahoo.com> wrote:
>
> > I have a table ordered by date. �When I find a specific record in this
> > set, I want to know what its position is in this ordered list.
>
> > I could get the date from the record found and count dates above or
> > below, but is there a better way?
>
> > Thank you.
>
> Look up the rownum psuedo column in your SQL manual.
>
> Basically select rownum, date_col from ( select date_code from t order
> by 1 )
>
> HTH -- Mark D Powell --

OK, came up with this

SELECT mypos
  FROM ( SELECT ROWNUM mypos, q.*

              FROM   tOne q
             WHERE   q.keyid1n = 201574
          ORDER BY   makeDate ) myrows, tOne x

 WHERE x.key = myrows.key AND x.flecth IS NULL;

thanks Received on Thu Jan 28 2010 - 13:11:07 CST