Solved

syntax for Proc Sql to get row_number() in Oracle

Posted on 2014-03-24
4
954 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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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 31

Accepted Solution

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

Author Comment

by:morinia
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now