但你还有手术室。
如果两个或都满意，您将获得两行。

with T1 as (
Select
Dept_No,
Product_No,
Order_No,
Order_Type
Row_number() over (partition by Product_ID order by Order_No desc) as "COUNT"
From Orders_Table
where "Order_Type" <> 'Cancel')

Select * from T1
where "COUNT"  = '1'

字符串

使用case表达式来控制哪些行从row_number（）获得结果，这里我们避免对任何已取消的行进行编号：

WITH t1 AS (
      SELECT
            Dept_No
          , Product_No
          , Order_No
          , Order_Type
            /*  the most recent order that was not canceled */
          , case when order_type <> 'Cancel' 
                 then ROW_NUMBER() OVER (PARTITION BY Product_ID 
                                          ORDER BY Order_No DESC)
                 end AS is_recent
       FROM Orders_Table
      )
SELECT
      *
FROM t1
WHERE is_recent = 1

字符串
或者，也许最简单的方法是简单地排除已取消的订单，例如：

WITH t1 AS (
      SELECT
            Dept_No
          , Product_No
          , Order_No
          , Order_Type
          , ROW_NUMBER() OVER (PARTITION BY Product_ID 
                                          ORDER BY Order_No DESC)
                 AS is_recent
       FROM Orders_Table
       WHERE order_type <> 'Cancel' 
      )
SELECT
      *
FROM t1
WHERE is_recent = 1

型
nb：row_number（）返回整数，所以不要将该列与字符串进行比较