Re: find position of row in set of rows

>> 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 -

Analytics to the rescue:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions137.htm#i86310

Kind regards

        robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/