CODEWITHSUNDEEP
sqloracle-databasesequence
user2297612
user2297612

Reputation: 1

Make a Name Sequence

I have table like this

file_nbr    name_seq    Person_name

10  1   James Linson

10  1   Ronn Dave

10  1   Michael Meyer

12  1   Pamela  J. Mayberry  

12  1   Randall M. Bachtel 

12  1   Cleary E. Mahaffey 

12  1   D. Scott Rowley

12  1   Stephen  L. Phelps 

12  1   Mark A. Bennet

12  1   Richard  P. Lewis

I want to change the name_seq, so the result like this:

10  1   James Linson

10  2   Ronn Dave

10  3   Michael Meyer

12  1   Pamela  J. Mayberry  

12  2   Randall M. Bachtel 

12  3   Cleary E. Mahaffey 

12  4   D. Scott Rowley

12  5   Stephen  L. Phelps 

12  6   Mark A. Bennet

12  7   Richard  P. Lewis

What's the best SQL query?

Upvotes: 0

Views: 70

Answers (1)

John Woo
John Woo

Reputation: 263723

WITH records
AS
(
    SELECT  "file_nbr",
            ROW_NUMBER() OVER(PARTITION BY "file_nbr" ORDER BY "file_nbr") "name_seq",
            "Person_name"
    FROM    TableName
)
SELECT  "file_nbr", "name_seq", "Person_name"
FROM    records

Upvotes: 4

Related Questions