syntax for Proc Sql to get row_number() in Oracle

Experts,

Can someone tell me the syntax to get the row_number reading in Oracle table using SQL.
I have about 50 columns in the select, and I know there is a way, but I can' remember the exact syntax.

It looks something like
SELECT T1.member_id, ROW_NUMBER() OVER (ORDER BY T1.member_id) AS ROWNUM FROM
          (Select fielda, fieldb, fieldc   etc.
              FROm TABLEA);
It is the second select that has all the fields, the first select just had the fields for ordering.
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
 
awking00Commented:
Why not just -
select member_id, <other fields>, row_number() over (order by member_id) row_num
from members;
0
 
awking00Commented:
You're aliasing a table T1, but not selecting from it. Does tableA contain the member_id as well? The syntax for the analytic row_number() looks fine as you have shown it, although the member_id needs to be in the table (or subselect) in the from clause and I don't think you can alias it with rownum, since that is a keyword with a special meaning.
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
Yes all of the fields in the first select are in the subselect.

So it would be more like:

SELECT T1.member_id, ROW_NUMBER() OVER (ORDER BY T1.member_id) AS ROWNUM FROM
          (Select member_id
                   , member_lname
                   , member_fname
                   , member_MI
                   ......more fields here
               FROM members t1);
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
I will try that.  

I must have patterned if after another query years back and thought only the partition criteria should be in the first select.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.