Filtering by RANK () in HAVING clause without subqueries
I get group maximums ( 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 great , but I have to wrap the request with a call RANK()
inside a subquery because this alternative throws 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;
Window functions can only appear in SELECT or ORDER BY clauses.
Is there an alternative syntax to avoid this limitation, or is the subquery the only sane way?
The ultimate goal is to integrate this code into a broader set of dynamically generated SQL expressions. If I can keep one query, I can just define different parts (select, join tables where, group, have and order) with the arrays. Otherwise, I need to think about a serious rewrite.
source to share
you can use top 1 with links like below
select top (1) with ties country, colour, quantity,
rank() over (partition by country order by quantity desc, id) as position
from data
--group by country, id, colour, quantity
order by rank() over (partition by country order by quantity desc, id)
source to share
If you look at the performance differences, I still feel that the subquery approach is better than the above (1) anchored approach due to the sort operator below:
Even though the top (1) with bindings is more elegant ... Below are the performance differences based on execution plan snapshot
source to share