I created a table that joins Product information with Master ID table. Master ID is a foreign key common to product information. So each individual who has purchased multiple products will have a common Master ID. I wanted to get the earliest Product sold to customer - so I used ROW_NUMBER() PARTITION BY.

SELECT PRODUCT_NUM, CO_Cd, PROD_CD, Date,
MASTER_ID,
ROW_NUMBER() OVER (PARTITION BY MASTER_ID ORDER BY Date) ROW_NUM
FROM MyTable
ORDER BY MASTER_ID

However, I'm facing a scenario where two products were purchased on the same date. So SQL I suppose is randomly assigning ROW_NUM = 1 to whichever product. I want to Default ROW_NUM = 1 to STATIONERY and not PRINTER in such instances. I tried a Case Statement which PARTITION BY MASTER_ID CASE WHEN PROD_CD = 'STATIONERY' THEN 'PA' ORDER BY Date. Need more guidance on this.

Output wanted:

CodePudding user response：

You just had the CASE in the PARTITION BY, it should have been in the ORDER BY

SELECT
  PRODUCT_NUM,
  CO_Cd,
  PROD_CD,
  Date,
  MASTER_ID,
  ROW_NUMBER() OVER (PARTITION BY MASTER_ID
    ORDER BY Date, CASE WHEN PROD_CD = 'STATIONERY' THEN 1 ELSE 2 END) ROW_NUM
FROM MyTable
ORDER BY MASTER_ID

CodePudding user response：

You can include prod_cd in the order by. For this purpose, 'STATIONERY' > 'PRINTER' and you want it first, so a descending sort is needed:

SELECT PRODUCT_NUM, CO_Cd, PROD_CD, Date, MASTER_ID,
       ROW_NUMBER() OVER (PARTITION BY MASTER_ID ORDER BY Date, PROD_CD DESC) AS ROW_NUM
FROM MyTable
ORDER BY MASTER_ID;