sql - I have a unique or distinct recors in all the column, table

Question

I need a unique record all the table and all the column how can I get

Table1 Col1 col2 col3 col4 col5

1.    A.     P.      20.   21
   

2.    B.     Q.     18.   19
   

3.    C.     R       17.   16
   

Table2 Col1 col2 col3 col4 col5

1.    A.     P.      51   58
   

2.    B.     Q.     60   65
   

3.    C.     R       18   25
   

4.    D.     S.      33.   31
   

Table3 Col1 col2 col3 col4 col5

1.    A.     P.      60.    13
   

2.    B.     Q.     75      61
   

3.    E       t      100   108
   

4.    F.      U.      91.   98
   

Output of table (I need like dz output)

Col1 col2 col3 col4 col5

1.    A.     P.      20.   21
   

2.    B.     Q.     18.   19
   

3.    C.     R       17.   16
   

4.    D.     S.      33.   31
   

5.    E.     T.       100  108
   

6.    F.     U.       91.   98
   

question from:https://stackoverflow.com/questions/65897331/i-have-a-unique-or-distinct-recors-in-all-the-column-table

Answer 1

To get all the unique rows you can combine all the records from all three tables using union all and use distinct to get unique rows.

select distinct col2 ,col3 ,col4 ,col5 from (
select col2 ,col3 ,col4 ,col5 from Table1 
union all
select col2 ,col3 ,col4 ,col5 from Table2
union all
select col2 ,col3 ,col4 ,col5 from Table3 )T

Or if you wan to show first record of each unique col1 and col2 value as in your output table use below query (I didn't use Col1 in the query since it's serial number for that table only):

with cte as (
select row_number() over (partition by Col2,Col3 order by sl)rn, col2 ,col3 ,col4 ,col5 from (
    select 1 sl, col2 ,col3 ,col4 ,col5 from Table1 
    union all
    select 2 sl, col2 ,col3 ,col4 ,col5 from Table2
    union all
    select 3 sl ,col2 ,col3 ,col4 ,col5 from Table3 )T
)select row_number() over (order by col1),Col1,Col2, Col3, Col4 from cte where rn=1