Fetch rows with same id and different prod_id

user_9135031 0
Viewed 49

I have two tables: tbltest1 and tbltest2

I want all the distinct rows of both tables, except the ones that have null in prod_id unless there is not any row in both tables with the same id with a not null prod_id

I tried to make a set with all the values then DISTINCTed to take only the unique ones and after used ROWNUMBER() OVER().:

with p as(
select t.*
from tbltest1 as t
union all
select d.*
from tbltest2 as d
),
s as (
select distinct colb, num,
ROW_NUMBER() OVER (PARTITION BY num ORDER BY colb DESC) as rnk
from p
)select *
from s
where rnk = 1

How can I achieve that? Is there also any other more efficient way to do it instead of this logic?

impala union sql
1 Answers

Use UNION for the 2 tables to remove the duplicates (if any) and then NOT EXISTS:

WITH cte AS (
  SELECT prod_id, dn FROM tbltest2
  UNION
  SELECT prod_id1, dn1 FROM tbltest1
)
SELECT c1.*
FROM cte c1
WHERE c1.prod_id IS NOT NULL
   OR NOT EXISTS (SELECT 1 FROM cte c2 WHERE c2.dn = c1.dn AND c2.prod_id IS NOT NULL)

See the demo.

user_104988280
Related