I'm struggling with a sql query, and will appreciate any help.

I have two tables, they both have a sort column. The first one looks like this:

And the second table like this

I want to be able to select the max sort_number from the table1 and make the first sort_number in table2 (house.png) to become 3, and the sort_number for car.png) to become 4.

Essentially, what I'm looking to achieve is an insert statement that selects from table2 and insert into table1, but I need the sort_number not to have duplicate, so the starting sort_number from the table2 should be the max of table1 1...and so on. If table1 does not have the person, I simply insert and not change the sort_number value.

I would appreciate of someone can help me please.

CodePudding user response：

Here's one way:

With grouped as
(Select person_id, max(sort_num) as maxsort
 From table1
 Group by person_id)

Select t2.person_id, t2.advert, t2.sort_num   coalesce(g.maxsort,0) as newsortnum
From table t2
Left join grouped g on t2.person_id = g.person_id

This will get max value of sort number for each key in the first table, and then attempt to join the second table to this grouped dataset. If there is a match, you add your second table's value to the max, and retain the value from the second table otherwise.

CodePudding user response：

You could try using UNION ALL and ROW_NUMBER:

WITH CTE AS
(
    SELECT
        person_id,
        image_name,
        sort_number,
        1 sort_table
    FROM dbo.Table1 t1

    UNION ALL

    SELECT 
        person_id,
        advert,
        sort_number,
        2
    FROM dbo.Table2 t2
)
SELECT 
    person_id,
    image_name,
    ROW_NUMBER() OVER(PARTITION BY person_id ORDER BY sort_table, sort_number) sort_number
FROM CTE
;

CodePudding user response：

insert into table2(person_id, advert, sort_number)
select table1.person_id, table1.image_name, table1.sort_number   table2.sort_number
from table1
join table2
on 1 = 1
left join table2 newer
on table2.sort_number < newer.sort_number
left join table1 mismatch
on table2.person_id = mistmatch.person_id and table2.advert = mismatch.image_name
where newer.person_id is null and mismatch.person_id is null

Firs join: we need to pair table1 and table2

Second join: we make sure that table2 record in the pair is the newest

Third join: we make sure that we do not insert what's already there.