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.
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.
awking00
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.
ASKER
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);
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);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
I must have patterned if after another query years back and thought only the partition criteria should be in the first select.