#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    1
    Rep Power
    0

    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.
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    What do want the output to look like?

IMN logo majestic logo threadwatch logo seochat tools logo