question

jaycock avatar image
jaycock 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.
row_number
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That's exactly what I need! Thanks Kev.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.