How to do a row number on UNION of 3 queries
I am trying to write a query of row number function in aws athena with 3 union queries, but my query fails stating “only one select statement allowed”. Any help would be appreciated as I am new to sql eg:
(
select id, name, address from table 1
) UNION (
select id, name, address from table 2
) UNION (
select * (select id, name, address, row_number() over (partition by id order by id) from table 3
) as "t" where "t".row_num =1;
)
Note: I am doing a row_num on 3rd table here because i want only unique values from 3rd table and distinct doesnt work directly on column using athena
