默认ROW_NUMBER为一个Product

Question

我创建了一个表，将Product信息与Master ID表连接起来。主ID是产品信息通用的外键。因此，购买了多个产品的每个人都将拥有一个通用的Master ID。我想要得到最早卖给客户的产品——所以我使用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

但是，我面临的情况是在同一天购买了两个产品。所以SQL我想是随机分配ROW_NUM = 1到任何产品。我想默认ROW_NUM = 1文具而不是PRINTER。我尝试了一个Case语句，当PROD_CD = 'STATIONERY'然后'PA' ORDER BY Date时，该PARTITION BY MASTER_ID Case。需要更多的指导。

<表类>PRODUCT_NUMCO_CdPROD_CDMASTER_ID日期ROW_NUMtbody><<tr>1854MAWC打印机100034930391/1/202111567参照文具100034930391/1/202122151PSIA文具100034972903/2/20211

你刚才把CASE放在了PARTITION BY里，它应该在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

可以在order by中包含prod_cd。出于这个目的，'STATIONERY' > 'PRINTER'和你想要它首先，所以需要降序排序:

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;