SQL conundrum, how to select latest date for part, but only 1 row per part (unique)

Question

I am trying to wrap my head around this one this morning.

I am trying to show inventory status for parts (for our products) and this query only becomes complex if I try to return all parts.

Let me lay it out:

• single table inventoryReport
• I have a distinct list of X parts I wish to display, the result of which must be X # of rows (1 row per part showing latest inventory entry).
• table is made up of dated entries of inventory changes (so I only need the LATEST date entry per part).
• all data contained in this single table, so no joins necessary.

Currently for 1 single part, it is fairly simple and I can accomplish this by doing the following sql (to give you some 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

that gets me my TOP 1 row, so simple per part, however I need to show all X (lets say 30 parts). So I need 30 rows, with that result. Of course the simple solution would be to loop X# of sql calls in my code (but it would be costly) and that would suffice, but for this purpose I would love to work this SQL some more to reduce the x# calls back to the db (if not needed) down to just 1 query.

From what I can see here I need to keep track of the latest date per item somehow while looking for my result set.

I would ultimately do a

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

to limit the parts I need. Hopefully I made my question clear enough. Let me know if you have an idea. I cannot do a DISTINCT as the rows are not the same, the date needs to be the latest, and I need a maximum of X rows.

Thoughts? I'm stuck...

Fpc

Answer 1

EDIT: Be sure to test the performance of each solution. As pointed out in this question, the CTE method may outperform using 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

Llanes

Answer 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

Aidaaidan

Answer 3

You need to join into a Sub-query:

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

Profiteer