I have this query:

select  b.Project_Id,b.Id,
FIRST_VALUE(i.Number) OVER(PARTITION BY b.id ORDER BY i.Date) as FirstNumDDT
,sum( ir.Qty) OVER(PARTITION BY b.id ORDER BY i.date)  as sumqty
from InvoiceRow ir 
inner join Invoice i on i.id=Invoice_Id 
inner join BillOfMaterial b on b.id=ir.BOM_Id

and a few part of the result is:

I need only the last row. How can I filter? Thanks

CodePudding user response：

Use TOP(N) in combination with an ORDER BY clause.

SELECT TOP(1) 
       b.Project_Id,b.Id,
       FIRST_VALUE(i.Number) OVER(PARTITION BY b.id ORDER BY i.Date) as FirstNumDDT,
       SUM(ir.Qty) OVER(PARTITION BY b.id ORDER BY i.date)  as sumqty
from InvoiceRow ir 
INNER JOIN Invoice i on i.id=Invoice_Id 
INNER JOIN BillOfMaterial b on b.id=ir.BOM_Id
ORDER BY sumqty DESC

If you want allow to retrieve all values that have the maximum sumqty values, you need to use TOP(1) WITH TIES instead.

SELECT TOP(1) WITH TIES
       b.Project_Id,b.Id,
       FIRST_VALUE(i.Number) OVER(PARTITION BY b.id ORDER BY i.Date) as FirstNumDDT,
       SUM(ir.Qty) OVER(PARTITION BY b.id ORDER BY i.date)  as sumqty
from InvoiceRow ir 
INNER JOIN Invoice i on i.id=Invoice_Id 
INNER JOIN BillOfMaterial b on b.id=ir.BOM_Id
ORDER BY sumqty DESC

CodePudding user response：

You can add a ROW_NUMBER() function to your query and filter the results based on the row number

select b.Project_Id, b.Id, FIRST_VALUE(i.Number) OVER(PARTITION BY b.id ORDER BY i.Date) as FirstNumDDT, 
sum(ir.Qty) OVER(PARTITION BY b.id ORDER BY i.date) as sumqty,
ROW_NUMBER() OVER(PARTITION BY b.id ORDER BY i.date DESC) as row_num
from InvoiceRow ir 
inner join Invoice i on i.id=Invoice_Id 
inner join BillOfMaterial b on b.id=ir.BOM_Id
WHERE row_num = 1;