sql - 在 ROW_NUMBER 之后添加 MAX 函数

Question

我有以下代码：

SELECT 
    * 
FROM 
    (SELECT 
         ROW_NUMBER () OVER (PARTITION BY PO_Number, PO_Value, Supplier 
                             ORDER BY Date_PurchaseOrder) AS Line_Count, 
         * 
     FROM 
         Table_PO) AS T 
WHERE
    Line_Count = 1 

它Line_Count相应地返回。但不是WHERE子句，我想使用一个MAX函数来只返回最高计数。我尝试了几个样品，但没有奏效。

我需要使用最高Date_PurchaseOrder的日期并忽略其他日期。

作为一个PO_Number可以有多个Date_PurchaseOrder，我只需要列中的最后一个日期。

提前致谢。

标签： sqlsql-servertsql

Answer 1

改用subquery：

select tp.*
from Table_PO tp
where Date_PurchaseOrder = (select max(Date_PurchaseOrder)
                            from Table_PO tp1
                            where tp1.PO_Number = tp.PO_Number
                           );

但是，您也可以使用dense_rank()函数：

select *
from (select *, dense_rank() over (partition by PO_Number 
                                        order by Date_PurchaseOrder desc) as Seq
      from Table_PO 
     ) tp
where Seq = 1;

编辑：只需按以下顺序订购DESC

SELECT * 
FROM (SELECT *, 
             ROW_NUMBER() OVER (PARTITION BY PO_Number, PO_Value, Supplier 
                                     ORDER  BY Date_PurchaseOrder DESC) AS Line_Count
      FROM Table_PO 
      ) AS T 
Where Line_Count = 1;