Filtering by RANK() in HAVING clause without subqueries

Question

I'm fetching group-wise maximums (the data CTE emulates a set of joins from actual tables):

with data as (
    select 'Austria' as country, 1 as id, 'red' as colour, 120 as quantity
    union all select 'Austria', 2, 'green', 96
    union all select 'Austria', 3, 'blue', 103

    union all select 'Belgium', 1, 'red', 33
    union all select 'Belgium', 2, 'green', 12
    union all select 'Belgium', 3, 'blue', 40
)
select country, colour, quantity
from (
    select country, colour, quantity,
    rank() over (partition by country order by quantity desc, id) as position
    from data
    group by country, id, colour, quantity
) subquery
where position=1;

This works fine but I'm forced to wrap the query with the RANK() call inside a subquery, because this alternative triggers a syntax error:

-- [...]
select country, colour, quantity,
rank() over (partition by country order by quantity desc, id) as position
from data
group by country, id, colour, quantity
having rank() over (partition by country order by quantity desc, id)=1;

Windowed functions can only appear in the SELECT or ORDER BY clauses.

Is there an alternative syntax to avoid this limitation or the subquery is the only sensible way?

The ultimate goal is to integrate this code into a larger set of dynamically generated SQL expressions. If I can keep a single query I can just define the different parts (select, joined tables, where, group by, having and order by) with arrays. Otherwise I need to think a major rewrite.

Answer 1

If you look at performance differences i still feel subquery approach is better than the above top (1) with ties approach because of below sort operator:

Eventhough top (1) with ties is more elegant... Below are the performance differences based on Execution plan snapshot