CODEWITHSUNDEEP
sqlsql-server
theo
theo

Reputation: 299

SQL Server - How to get the latest update of each group from multiple tables

For example, I have 3 tables:

tbl_main
-------------------------
|ID_No |Info1   |Info2  |
|------+--------+-------|
|00123 |name1   |a city |
|00567 |name2   |b city |
|00789 |name3   |c city |
-------------------------

tbl_version
--------------------------------
|ID_No |Version_Name|Version_No|
|------+------------+----------|
|00123 |version_a_01|1         |
|00567 |version_b_01|1         |
|00789 |version_c_01|1         |
--------------------------------

tbl_version_upd
--------------------------------
|ID_No |Version_Name|Version_No|
|------+------------+----------|
|00123 |version_a_02|2         |
|00123 |version_a_03|2         |
|00123 |version_a_04|4         |
|00789 |version_c_02|2         |
--------------------------------

I want to get the latest Version_Name of every ID_No based on Version_No from tbl_version and tbl_version_upd combined.

Wanted output:
--------------------------------------
|ID_No |Info1   |Info2  |Version_Name|
|------+--------+-------+------------|
|00123 |name1   |a city |version_a_04|
|00567 |name2   |b city |version_b_01|
|00789 |name3   |c city |version_c_02|
--------------------------------------

Currently, I have this query:

Select a.ID_No, a.Info1, a.Info2, b.Version_Name
FROM tbl_main a
LEFT JOIN (tbl_version UNION ALL tbl_version_upd) b ON a.ID_No=b.ID_No

But it gets all versions of each ID_No

Current output:
--------------------------------------
|ID_No |Info1   |Info2  |Version_Name|
|------+--------+-------+------------|
|00123 |name1   |a city |version_a_01|
|00123 |name1   |a city |version_a_02|
|00123 |name1   |a city |version_a_03|
|00123 |name1   |a city |version_a_04|
|00567 |name2   |b city |version_b_01|
|00789 |name3   |c city |version_c_01|
|00789 |name3   |c city |version_c_02|
--------------------------------------

Upvotes: 0

Views: 38

Answers (2)

Daniel Brughera
Daniel Brughera

Reputation: 1651

An apply is a good way to get it, I also used a CTE for the union, but just for readability, the subquery works as well 

;WITH allversions AS (
    SELECT ID_No, Version_Name, Version_No
    FROM tbl_version
    UNION ALL 
    SELECT ID_No, Version_Name, Version_No
    FROM tbl_version_upd
)
SELECT a.ID_No, a.Info1, a.Info2, b.Version_Name
FROM tbl_main a
OUTER APPLY (
    SELECT TOP 1 Version_Name 
    FROM allversions av
    WHERE av.ID_No = a.ID_No
    ORDER BY Version_No DESC
) b

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

You can try below using a correlated subquery

Select a.ID_No, a.Info1, a.Info2, b.Version_Name
FROM tbl_main a
LEFT JOIN (tbl_version UNION ALL tbl_version_upd) b ON a.ID_No=b.ID_No
where version_no in 
      (select max(version_no) from (tbl_version UNION ALL tbl_version_upd) b1 
           where b.ID_No=b1.ID_No)

Upvotes: 0

Related Questions