MegoSoft MegoSoft - 3 months ago 7
SQL Question

SQL Server : connect two results with specific row

I have a table of users and a table of their contracts. I have two selects. The first one, which selects all from users table and second one, which selects only new users (from another user table) for "today". I need to connect those two results, but there's a problem.

Each user had "rownumber" column, it depends by count of contracts for each user (each user can have 1 or more contracts). I need to get new rownumber for new row. See example

The first select returns:

user_id | contract_id | rownumber
1 456 1
1 457 2
1 699 3


The second select selects new rows for those users:

user_id | contract_id
1 1024
1 | 1079


And I need to connect those results to get this end result:

user_id | contract_id | rownumber
1 456 1
1 457 2
1 699 3
1 1024 4
1 1079 5


How can I do that?

Answer
;with cte
as
(
select 
user_id,contractid from table1
union all
select 
user_id,contractid from table2
)
select *,row_number() over (partition by user_id order by contractid ) from cte