Re: find position of row in set of rows

From: ddf <oratune_at_msn.com>
Date: Thu, 28 Jan 2010 12:31:30 -0800 (PST)
Message-ID: <56264a9b-cb5c-4cfa-8664-3b3d79c6a969_at_t34g2000prm.googlegroups.com>



On Jan 28, 2:41 pm, cate <catebekens..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

ROWNUM is assigned to the result set so the call should be located outside of an ordered subquery. Read here:

http://oratips-ddf.blogspot.com/2008/06/row-row-row.html

David Fitzjarrell Received on Thu Jan 28 2010 - 14:31:30 CST

Original text of this message