Share via

SQL Server: Take data whose date is max joining two tables

T.Zacks 3,996 Reputation points
2023-07-17T18:29:48.7733333+00:00

Suppose i have a two tables called Table1 and Table2.

both table has same structure mean same number of columns. sample structure look like

ID INT

Name VARCHAR(50)

Salary Decimal(18,2)

ModDate DateTime

Now i want to display records from both table just by joining and join key will be ID but i want to display those records whose date is max.

Say for example : Table1 and Table2 has a data whose id value is same but table2's ModDate field data is greater than Table1 then that records will be taken from table2 instead of table1.

please share a sample sql to achieve the output. based on Max ModDate records will be take either from Table1 or Table2.

Thanks

SQL Server | Other
0 comments
Accepted answer
  1. Anonymous
    2023-07-18T03:01:01.61+00:00

    Hi @T.Zacks

    Or you can try this query.

    ;with T1 as(
  select * from table1
  union all
  select * from table2
),T2 as(
  select *,row_number()over(partition by ID order by ModDate desc) as num from T1)
select ID,Name,Salary,ModDate from T2 where num = 1;

    Best regards,

    Percy Tang

    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    2 people found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2023-07-17T18:35:23.96+00:00

    It's pretty boring:

    SELECT coalesce(A.ID, B.ID) AS id, 
       CASE WHEN a.ModDate < b.ModDate THEN b.ModDate ELSE a.ModDate END AS ModDate,
       CASE WHEN a.ModDate < b.ModDate THEN b.Name  ELSE a.Name END AS Name,
       CASE WHEN a.ModDate < b.ModDate THEN b.Salary  ELSE a.Salary END AS Salary,
       --- etc
FROM   Table1 A
FULL JOIN Table2 B ON A.ID = B.ID
ORDER BY id

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.