Geek Answers Handbook

SQL puzzle how to choose the last date for a part, but only 1 row for each part (unique)

I'm trying to hug my head this morning.

I am trying to show the status inventoryfor parts (for our products), and this query becomes complicated if I try to return all parts.

Let me state this:

  • separate table inventoryReport
  • I have a list of X parts that I want to display, the result of which should be X # lines (1 line for each part showing the last inventory entry). Table
  • consists of dated inventory change records (so I only need a date record LATESTfor each part).
  • all data contained in this single table, so no joins are required.

Currently, for one separate part, this is quite simple, and I can accomplish this by doing the following sql (to give you an idea):

SELECT     TOP (1) ldDate, ptProdLine, inPart, inSite, inAbc, ptUm, inQtyOh + inQtyNonet AS in_qty_oh, inQtyAvail, inQtyNonet, ldCustConsignQty, inSuppConsignQty
FROM         inventoryReport
WHERE     (ldPart = 'ABC123')
ORDER BY ldDate DESC

which gets my TOP-1 line, so simple for every part, however I need to show all X (say 30 parts). So I need 30 lines, with this result. Of course, a simple solution would be to loop X # from sql calls in my code (but that would be expensive), and that would be enough, but for this purpose I would like to work on this SQL a bit more to reduce the x # callback db (if not needed) to a single request.

From what I see here, I need to somehow keep track of the last date for the item, looking for my own set of results.

I will eventually do

WHERE ldPart in ('ABC123', 'BFD21', 'AA123', etc)

. , . , . DISTINCT, , , X .

? ...

+5
3

EDIT. . , CTE , ROW_NUMBER.

;with cteMaxDate as (
    select ldPart, max(ldDate) as MaxDate
        from inventoryReport
        group by ldPart
)
SELECT md.MaxDate, ir.ptProdLine, ir.inPart, ir.inSite, ir.inAbc, ir.ptUm, ir.inQtyOh + ir.inQtyNonet AS in_qty_oh, ir.inQtyAvail, ir.inQtyNonet, ir.ldCustConsignQty, ir.inSuppConsignQty
    FROM cteMaxDate md
        INNER JOIN inventoryReport ir
            on md.ldPart = ir.ldPart
                and md.MaxDate = ir.ldDate
+2
  SELECT *
  FROM   (SELECT i.*,
      ROW_NUMBER() OVER(PARTITION BY ldPart ORDER BY ldDate DESC) r
      FROM   inventoryReport i
      WHERE  ldPart in ('ABC123', 'BFD21', 'AA123', etc)
         )
  WHERE  r = 1
+5

Sub-:

SELECT i.ldPart, x.LastDate, i.inAbc
FROM inventoryReport i
INNER JOIN (Select ldPart, Max(ldDate) As LastDate FROM inventoryReport GROUP BY ldPart) x
on i.ldPart = x.ldPart and i.ldDate = x.LastDate
+2

More articles:


All Articles