我正在获取逐组最大值（dataCTE从实际表中模拟一组联接）：

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;

这可以正常工作， 但是我不得不用RANK()子查询中的调用将查询包装起来，因为这种选择会触发语法错误：

-- [...]
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;

窗口函数只能出现在SELECT或ORDER BY子句中。

是否有其他语法可以避免此限制，否则子查询是唯一明智的方法？

最终目标是将这些代码集成到一组更大的动态生成的SQL表达式中。
如果我可以保留一个查询，则可以使用数组定义不同的部分（选择，联接表，位置，分组依据，具有和排序依据）。否则，我需要考虑进行重大重写。