Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


create temp table customer1(id text,
 	customer text, 	product text, 	quantity numeric);
insert into customer1 values
('001', 	'Cust001',	'Soap' ,	200),
('002', 	'Cust002',	'Shampoo', 	23),
('003', 	'Cust003' ,	'Ketchup', 	30);

create temp table customer2(id text,
 	customer text, 	product text, 	quantity numeric);
insert into customer1 values
('002','Cust002' ,	'Shampoo' ,	70),
('003',	'Cust003' ,	'Ketchup' ,	50);
3 rows affected

2 rows affected

with cte as
    (
(select  * from customer1 order by  quantity)
union all
(select * from customer2 order by  quantity))
select  distinct on (id,customer,product) * from cte order by 1,2,3,4 desc;


id
customer
product
quantity


001
Cust001
Soap
200

002
Cust002
Shampoo
70

003
Cust003
Ketchup
50


with cte as
    (
(select  * from customer1 order by  quantity)
union all
(select * from customer2 order by  quantity))
select  distinct on (id,customer,product) * from cte order by 1,2,3,4;


id
customer
product
quantity


001
Cust001
Soap
200

002
Cust002
Shampoo
23

003
Cust003
Ketchup
30