title: partition over two columns
body: <p>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</p>
  
  <pre><code>SELECT 
          RowNumber() over (PARTITION BY F13.DIM_PROJECT_ID, F13.CATEGORY_NAME ORDER BY F13.PROJECT_NAME),      
          F13.DIM_PROJECT_ID.....etc.
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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 )    
  </code></pre>
  
  <p>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:
  <img src="https://i.stack.imgur.com/E8Hp1.png" alt="enter image description here"></p>
  
  <p>Desired results:</p>
  
  <p><img src="https://i.stack.imgur.com/3hHfE.png" alt="enter image description here"></p>