求SQL 语句

[ballatong]

我已经从两者表中关联到了数据，使用的语句如下：
select FF.BatchID,ff.[Order],kk.Description,ff.CurrentProcess,ff.CurrentProcessNumber from NewProcessTracking as FF left join NewBatchTracking as KK
on ff.BatchID=KK.BatchBarcode where ff.ScheduleDate is not null

显示结果如下图：



现在想要的结果是在这个select上再过滤 BatchID重复行，取 CurrentProcessNumber 最小的，请问怎么实现？

[卖水果的net]

-- 大概这个样子 

select * from (
select row_number() over(partition by BatchID order by CurrentProcessNumber) rn 
from xxx 
) x 
where rn = 1 

[Neo_whl]

--参考：
    select     
    FF.BatchID,ff.[Order],
    kk.Description,
    ff.CurrentProcess,
    ff.CurrentProcessNumber,
    ROW_NUMBER() over(partition by Batchid order by CurrentProcessNumber)  as number
from(
    select 
    FF.BatchID,ff.[Order],
    kk.Description,
    ff.CurrentProcess,
    ff.CurrentProcessNumber,
    ROW_NUMBER() over(partition by Batchid order by CurrentProcessNumber)  as number
from  
NewProcessTracking as FF left join NewBatchTracking as KK on ff.BatchID=KK.BatchBarcode where ff.ScheduleDate is not null )t
 where number=1

[ballatong]

楼上的报错啊 Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "FF.BatchID" could not be bound. Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "ff.Order" could not be bound. Msg 4104, Level 16, State 1, Line 4 The multi-part identifier "kk.Description" could not be bound. Msg 4104, Level 16, State 1, Line 5 The multi-part identifier "ff.CurrentProcess" could not be bound. Msg 4104, Level 16, State 1, Line 6 The multi-part identifier "ff.CurrentProcessNumber" could not be bound.

[spiritofdragon]

select 
	t.BatchID,t.[Order],t.Description,t.CurrentProcess,t.CurrentProcessNumber  
from(
	select FF.BatchID,ff.[Order],kk.Description,ff.CurrentProcess,ff.CurrentProcessNumber  
		,ROW_NUMBER()over(partition by FF.BatchID order by ff.CurrentProcessNumber) rn
	from  NewProcessTracking as FF 
	left join NewBatchTracking as KK on ff.BatchID=KK.BatchBarcode 
	where ff.ScheduleDate is not null 
	) t
where rn=1
	

[ballatong]

楼上正确！ 谢谢， 结贴给分