CODEWITHSUNDEEP
sqlsql-servert-sql
Francisco
Francisco

Reputation: 11

ADD MAX function after ROW_NUMBER

I have the following code:

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

It returns the Line_Count accordingly. But instead of the WHERE clause, I would like to use a MAX function to return only the highest counts. I tried a few samples but didn't work.

I need to get use the highest Date_PurchaseOrder and disregard the other dates.

As a PO_Number can have multiple Date_PurchaseOrder, I need only the last date in the column.

Thanks in advance.

Upvotes: 1

Views: 405

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Use subquery instead :

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

However, you can also use dense_rank() function :

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

EDIT : Just do order by 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;

Upvotes: 1

Related Questions