January 2nd, 2013, 03:17 PM
-
SQL - Using Row Number over Partition By: with Grouping Sets
Hello all,
I am trying to write a single SELECT statement that groups at 2 levels of aggregation (using grouping sets) and assigns row numbers (to rank each item) that are partitioned at the correct level for each grouping set. I have the grouping sets figured out but I can't find a way to make Partition By match each level of aggregation.
What I am looking for (in a single SELECT statement) is logically equivalent to:
Code:
SELECT week
,region
,NULL as country
,item
,SUM(qty)
,ROW_NUMBER OVER (PARTITION BY week, region ORDER BY SUM(qty) DESC) as rownum
FROM base
GROUP BY
week
,region
,item
UNION ALL
SELECT week
,NULL as region
,country
,item
,SUM(qty)
,ROW_NUMBER OVER (PARTITION BY week, country ORDER BY SUM(qty) DESC) as rownum
FROM base
GROUP BY
week
,country
,item
I hoped that I could do something like this:
Code:
SELECT week
,region
,country
,item
,SUM(qty)
,ROW_NUMBER OVER (PARTITION BY week, GROUPING SETS (region, country) ORDER BY SUM(qty) DESC) as rownum
FROM base
GROUP BY
week
,GROUPING SETS (region, country)
,item
But it looks like I am not allowed to partition by grouping sets -- I get the error ORA-00907: missing right parenthesis. I didn't expect it to work but I am not sure how else to partition by multiple levels.
Any help would be greatly appreciated! Please let me know if I could have tagged my code or met other forum standards better.
January 3rd, 2013, 10:30 PM
-
What do want the output to look like?