Solved

syntax for Proc Sql to get row_number() in Oracle

Posted on 2014-03-24
4
1,035 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
  • 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 350 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

680 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