Re: find position of row in set of rows

From: cate <catebekensail_at_yahoo.com>
Date: Thu, 28 Jan 2010 11:41:05 -0800 (PST)
Message-ID: <8a620b5b-4937-4bb4-9cc1-be4f45416baa_at_g29g2000yqe.googlegroups.com>

On Jan 28, 1:17�pm, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> On Jan 28, 2:11�pm, cate <catebekens..._at_yahoo.com> wrote:
>
>
>
> > 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- Hide quoted text -
>
> > - Show quoted text -
>
> Warning the rownum may be assigned before the sort in the SQL you
> posted. �You should assign it to the ordered data if you want the
> actual relative from the first sorted row order.
>
> HTH -- Mark D Powell --

You have me. How would I secure the rownumbers? I'll reveiw your first suggestion. Shoot, I thought I was there!. Thanks. Received on Thu Jan 28 2010 - 13:41:05 CST