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