?
Solved

syntax for Proc Sql to get row_number() in Oracle

Posted on 2014-03-24
4
Medium Priority
?
1,137 Views
Last Modified: 2014-03-25
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.
0
Comment
Question by:morinia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 39951363
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
 

Author Comment

by:morinia
ID: 39951401
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
 
LVL 32

Accepted Solution

by:
awking00 earned 1400 total points
ID: 39951438
Why not just -
select member_id, <other fields>, row_number() over (order by member_id) row_num
from members;
0
 

Author Comment

by:morinia
ID: 39951483
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

Featured Post

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question