Re: find position of row in set of rows

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Thu, 28 Jan 2010 11:17:55 -0800 (PST)
Message-ID: <9616d2d1-5e49-4f01-8e13-b3b8fae3cee1_at_a32g2000yqm.googlegroups.com>

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 -- Received on Thu Jan 28 2010 - 13:17:55 CST