如何拉取组的第一行并使用过滤器使用"not"获取结果

Question

我正在尝试拉组的第一行。对于我的问题，我试图按预定的胎面代码排序，然后是否预定或替代，只拉组的第一行。我也试图过滤Loc.BKT不是HDL。

我就是这样做的，但是行不通。

Select FAC,
Cosw.Tread_Code as Scheduled_Tread_Code,
SPEC_NEW.Tread_Code,
Case When cosw.tread_code = spec_new.tread_code Then 'Scheduled' 
else 'Alternative' end AS Size, 
COUNT(Distinct loc.serial )  QTY 
FROM [TireTrack].[dbo].[cos_work] cosw with (nolock)
Inner Join [SharedData].dbo.spec_master Spec with (nolock) On spec.spec=Cosw.SPEC
Inner Join [SharedData].dbo.spec_master SPEC_NEW with (nolock) On SPEC_NEW.ARTICLE=SPEC.article
Inner Join [DataWarehouse].[dbo].[Locator] LOC with (Nolock) ON LOC.SPEC=SPEC_NEW.SPEC
Where Cosw.FAC='pe19' and Loc.BKT not in ('HDL') 
GROUP BY FAC, cosw.Tread_Code, SPec_new.Tread_Code

Answer 1

谢谢你@Brad，我是通过这样做才成功的

ROW_NUMBER() OVER (
PARTITION BY FAC, Cosw.Tread_Code
ORDER BY fac, Cosw.Tread_Code,Case When cosw.tread_code = spec_new.tread_code Then 'Scheduled' else 'Alternative' end DESC
) AS r_num