Re: find position of row in set of rows
From: cate <catebekensail_at_yahoo.com>
Date: Thu, 28 Jan 2010 13:55:54 -0800 (PST)
Message-ID: <565e3a7b-b214-4438-9af4-793e36ffa3ed_at_g29g2000yqe.googlegroups.com>
On Jan 28, 3:39 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 01/28/2010 09:31 PM, ddf wrote:
>
>
>
> > 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
>
> Analytics to the rescue:
>
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
>
> Kind regards
>
> robert
>
> --
> remember.guy do |as, often| as.you_can - without endhttp://blog.rubybestpractices.com/
Date: Thu, 28 Jan 2010 13:55:54 -0800 (PST)
Message-ID: <565e3a7b-b214-4438-9af4-793e36ffa3ed_at_g29g2000yqe.googlegroups.com>
On Jan 28, 3:39 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 01/28/2010 09:31 PM, ddf wrote:
>
>
>
> > 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
>
> Analytics to the rescue:
>
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
>
> Kind regards
>
> robert
>
> --
> remember.guy do |as, often| as.you_can - without endhttp://blog.rubybestpractices.com/
YES. Thanks all. Received on Thu Jan 28 2010 - 15:55:54 CST