asked
How do I assign a group id to each recordset using row_number function?
Hello, I am trying to assign a group id to each set of records that are returned using row_number function in a query. The table definition is: CREATE TABLE User_Table ( reg_user_id INTEGER PRIMARY KEY, Fname VARCHAR(15), Lname VARCHAR(15), Email VARCHAR(25) ) GO --the values are: INSERT INTO User_Table VALUES(123455,'FRED','SMITH',
'eioio@gmail.com'); INSERT INTO User_Table VALUES(123456,'FRED','SMITH',
'eioio@gmail.com'); INSERT INTO User_Table VALUES(123457,'FRED','SMITH',
'eioio@gmail.com'); INSERT INTO User_Table VALUES(123458,'BOB','JONES',
'bjones@gmail.com'); INSERT INTO User_Table VALUES(123459,'BOB','JONES',
'bjones@gmail.com'); INSERT INTO User_Table VALUES(123460,'CHRISTY','BIGELOW',
'cbig@gmail.com'); INSERT INTO User_Table VALUES(123461,'Daisy','Cheek',
'dchick@gmail.com'); GO --the query: select reg_user_id,fname,lname,email, ROW_NUMBER() OVER(PARTITION BY fname,lname,email ORDER BY reg_user_id) as seq from User_Table GO What I am trying to do is get the following result so that I can populate a new table with this data. (Notice the new column group_id where each group is assigned a number based on the partition in the above query) group_id reg_user_id fname lname email seq 1 123458 BOB JONES
bjones@gmail.com 1 1 123459 BOB JONES
bjones@gmail.com 2 2 123460 CHRISTY BIGELOW
cbig@gmail.com 1 3 123461 Daisy Cheek
dchick@gmail.com 1 4 123455 FRED SMITH
eioio@gmail.com 1 4 123456 FRED SMITH
eioio@gmail.com 2 4 123457 FRED SMITH
eioio@gmail.com 3 Any suggestion would be appreciated. EDIT: This is my first post so I just re-formatted it a bit.
Thats the same as applying the dense_rank function over the same columns as the partition: select reg_user_id,fname,lname,email, ROW_NUMBER() OVER(PARTITION BY fname,lname,email ORDER BY reg_user_id) as seq, dense_rank()over(order by fname,lname,email) as group_id from User_Table
14 People are following this question.
