CODEWITHSUNDEEP
postgresqljoinunion
M.N. Waleedh
M.N. Waleedh

Reputation: 87

Combine two different tables Without Duplicating using Postgres SQL

I am working on a query where I should combine 2 tables and get each user as a separate entry (The user should not be duplicate). For the date, I need to get the latest out of those 2 tables

table 1

enter image description here

table 2

enter image description here

Expected output ( I need to combine both tables and get the data's of the user as a single entry and for the date, i need to get the latest date out of those 2 tables)

user_id      name        date     
----------------------------------
     1       John     2020-10-29 --The latest date-- 
     2       Tom      2020-11-15 --The latest date--   
     3       Peter    2020-12-10 --The latest date--

Actual Output

enter image description here

My postgresql

SELECT user_id, name, date
FROM
  table_1
UNION
SELECT user_id, name, date
FROM
table_2

I tried many ways but nothing worked. The datas are duplicating when doing the union. Can someone help me

Upvotes: 1

Views: 234

Answers (1)

Rahul Biswas
Rahul Biswas

Reputation: 3457

Use combine two tables using UNION ALL then apply ROW_NUMBER() for serializing user_id wise value with descending date. Then retrieve last record by using CTE. Using UNION ALL for avoiding extra ordering.

-- PostgreSQL
WITH c_cte AS (
    SELECT t.*
         , ROW_NUMBER() OVER (PARTITION BY t.user_id ORDER BY t.date DESC) row_num
    FROM (SELECT user_id, name, date
          FROM table_1

          UNION ALL

          SELECT user_id, name, date
          FROM table_2) t
)
SELECT user_id, name, date
FROM c_cte
WHERE row_num = 1
ORDER BY user_id

Also another way for doing same thing without CTE

SELECT u.user_id, u.name, u.date
FROM (SELECT t.*
           , ROW_NUMBER() OVER (PARTITION BY t.user_id ORDER BY t.date DESC) row_num
      FROM (SELECT user_id, name, date
            FROM table_1

            UNION ALL

            SELECT user_id, name, date
            FROM table_2) t
           ) u
WHERE u.row_num = 1
ORDER BY u.user_id

Upvotes: 1

Related Questions