Solved

syntax for Proc Sql to get row_number() in Oracle

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 - query 23 59
SQL Query including math and 2 tables problem 26 50
SQL Server 2012 rs - Field1 and Field2 add 1 to Total 4 18
run sql script from putty 4 35
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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
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.

786 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