UNION not working Teradata SQL. Syntax error: Top N option is not allowed in a query connected by set operators

Question

When I run both queries individually, they run correctly. But when I try to combine both result sets into one table using the UNION operator, it doesn't run and I get the error message : "Syntax error: Top N option is not allowed in a query connected by set operators."

select
    top 1
    city,
    count(*)
from unicorns
where city is not null and industry = 'Edtech'
group by city
order by 2 desc

union

select
    top 1
    city,
    count(*)
from unicorns
where city is not null and industry = 'Internet software & services'
group by city
order by 2 desc

I would appreciate any help, Thanks.

Answer 1

Instead you can use window functions to achieve the same:

select
    city,
    count(*) ccount
from unicorns
where city is not null and industry = 'Edtech'
group by city
QUALIFY ROW_NUMBER() OVER (ORDER BY ccount DESC) = 1

union

select    
    city,
    count(*) ccount
from unicorns
where city is not null and industry = 'Internet software & services'
group by city
QUALIFY ROW_NUMBER() OVER (ORDER BY ccount DESC) = 1

This way you aren't relying on ordering/top an interim result set (outside of partition ordering) and Teradata will be ok with it.