partition over two columns

Question

I'm wanting to partition by two columns (PROJECT_ID, AND CATEGORY_NAME) and I'm having trouble writing the correct syntax. My query below is functional but when I attempt to add an additional over clause it doesn't work correctly. The recursive query was used to concatenate rows partitioning over project_id, creating a list of admins combining and concatenating name_last and name_first to make a list. I need to use an additional over clause to include the CATEGORY_NAME due to admins in the list that work in different categories ('INVISION' AND 'INSIGHT') but are under the same project_id. The first subquery

SELECT 
        RowNumber() over (PARTITION BY F13.DIM_PROJECT_ID, F13.CATEGORY_NAME ORDER BY F13.PROJECT_NAME),      
        F13.DIM_PROJECT_ID.....etc.

extracts the correct data, I'm just unsure of how to pull that correct data out partitioning by both project and category. I'm using db2.

with

   t1(rowNum, PROJECT_ID, NAME_LAST, NAME_FIRST, POINT_OF_CONTACT, PROJECT_NAME, BUSINESS_NAME) as 

   (
      SELECT 
        RowNumber() over (PARTITION BY F13.DIM_PROJECT_ID, F13.CATEGORY_NAME ORDER BY F13.PROJECT_NAME),      
        F13.DIM_PROJECT_ID,
        F2P.NAME_LAST,
        F2P.NAME_FIRST, 
        REPLACE(F2P.POINT_OF_CONTACT, ',', ' |') AS POINT_OF_CONTACT,
        F13.PROJECT_NAME,
        F2H.CATEGORY_NAME,

FROM FACT_TABLE AS F13
INNER JOIN ADMIN AS F2P ON F13.DIM_PROJECT_ID = F2P.DIM_PROJECT_ID
LEFT JOIN HOURS AS F2H ON F13.DIM_PROJECT_ID = F2H.DIM_PROJECT_ID
WHERE F2H.CATEGORY_NAME = ('INVISION')




group by  
        F13.DIM_PROJECT_ID,
        F13.PROJECT_NAME,
        F2P.NAME_LAST,
        F2P.NAME_FIRST, 
        F2P.POINT_OF_CONTACT,
        F2H.CATEGORY_NAME
 ) ,

       t2(PROJECT_ID, LIST, POINT_OF_CONTACT, PROJECT_NAME, BUSINESS_NAME, cnt) AS
    ( SELECT    PROJECT_ID, 
                VARCHAR(NAME_FIRST CONCAT ' ' CONCAT NAME_LAST, 6000),
                POINT_OF_CONTACT, 
                PROJECT_NAME, 
                CATEGORY_NAME,
                1
    FROM t1
            WHERE rowNum = 1
            UNION ALL
    SELECT      t2.PROJECT_ID,
                t2.list || ' | ' || t1.NAME_FIRST CONCAT ' ' CONCAT t1.NAME_LAST, 
                t1.POINT_OF_CONTACT,
                t1.PROJECT_NAME, 
                t1.CATEGORY_NAME
    FROM t2, t1 
            WHERE t2.project_id = t1.project_id 
            AND   t2.cnt + 1 = t1.rowNum )    
    SELECT      PROJECT_ID, 
                PROJECT_NAME, 
                POINT_OF_CONTACT,
                CATEGORY_NAME
                list
    FROM t2
            WHERE ( PROJECT_ID, cnt ) IN (
    SELECT PROJECT_ID, MAX(rowNum)
    FROM t1
            GROUP BY PROJECT_ID )

The results that I'm getting are producing duplicates but only when the second column (category_name is included in the partition clause. Current results: enter image description here

Desired results:

enter image description here

Are you trying to get 2 different sets of row numbers, one counting based on each dim_project_id, and a second set of row numbers for each category_name? It's generally useful to show some example data and the expected results... — Ian Bjorhovde, Sep 16 '13 at 20:38
Initially that was my approach but I didn't realize multiple columns were supported by the partition clause. I changed my question above to reflect the change I made. Now I'm getting duplicate results. I will attach a sample and expected results. — Tone, Sep 16 '13 at 20:53

score 1 · Accepted Answer · answered Sep 17 '13 at 21:23

I figured it out. I added an ID for category and partitioned by category_id and project_id.

with

   t1(rowNum, PROJECT_ID, NAME_LAST, NAME_FIRST, POINT_OF_CONTACT, PROJECT_NAME, CATEGORY_ID, CATEGORY_NAME) as 

   (
      SELECT 
        RowNumber() over (PARTITION BY F13.DIM_PROJECT_ID, F13.CATEGORY_ID ORDER BY F13.PROJECT_NAME, F13.CATEGORY_NAME),      
        F13.DIM_PROJECT_ID,
        F2P.NAME_LAST,
        F2P.NAME_FIRST, 
        REPLACE(F2P.POINT_OF_CONTACT, ',', ' |') AS POINT_OF_CONTACT,
        F13.PROJECT_NAME,
        F13.CATEGORY_ID
        F13.CATEGORY_NAME,

FROM FACT_TABLE AS F13
INNER JOIN ADMIN AS F2P ON F13.DIM_PROJECT_ID = F2P.DIM_PROJECT_ID
LEFT JOIN HOURS AS F2H ON F13.DIM_PROJECT_ID = F2H.DIM_PROJECT_ID
WHERE F13.CATEGORY_NAME = ('INVISION')




group by  
        F13.DIM_PROJECT_ID,
        F13.PROJECT_NAME,
        F2P.NAME_LAST,
        F2P.NAME_FIRST, 
        F2P.POINT_OF_CONTACT,
        F13.CATEGORY_ID
        F13.CATEGORY_NAME
 ) ,

       t2(PROJECT_ID, LIST, POINT_OF_CONTACT, PROJECT_NAME, CATEGORY_ID, CATEGORY_NAME, cnt) AS
    ( SELECT    PROJECT_ID, 
                VARCHAR(NAME_FIRST CONCAT ' ' CONCAT NAME_LAST, 6000),
                POINT_OF_CONTACT, 
                PROJECT_NAME, 
                CATEGORY_ID,
                CATEGORY_NAME,
                1
    FROM t1
            WHERE rowNum = 1
            UNION ALL
    SELECT      t2.PROJECT_ID,
                t2.list || ' | ' || t1.NAME_FIRST CONCAT ' ' CONCAT t1.NAME_LAST, 
                t1.POINT_OF_CONTACT,
                t1.PROJECT_NAME, 
                t1.CATEGORY_ID,
                t1.CATEGORY_NAME
    FROM t2, t1 
            WHERE t2.project_id = t1.project_id 
            AND t2.category_id = t1.category_id
            AND   t2.cnt + 1 = t1.rowNum )    
    SELECT      PROJECT_ID, 
                PROJECT_NAME, 
                POINT_OF_CONTACT,
                CATEGORY_ID,
                CATEGORY_NAME
                list
    FROM t2
            WHERE ( PROJECT_ID, CATEGORY_ID, cnt ) IN (
    SELECT PROJECT_ID, CATEGORY_ID, MAX(rowNum)
    FROM t1
            GROUP BY PROJECT_NAME )

partition over two columns

1 Answers1