Latest row for each combination of item and location

Question

I have a table named IJ with a datetime column IJDATE, and varchar columns IJLOC and IJITEM. I need the most recent row for each combination of those two columns (and include the other columns, like IJLCGT, an integer, for the most recent row). So for example, from this data:

IJLOC   IJITEM      IJDATE      IJLCGT
Reno    12X12X12PX  2013-07-29  175705
Reno    12X12X12PX  2013-08-14  125905

I want this result (since it is the most recent row for that combination):

IJLOC   IJITEM      IJDATE      IJLCGT
Reno    12X12X12PX  2013-08-14  125905

I've tried using MAX and grouping the varchar columns, but then I can't include the other columns.

score 4 · Accepted Answer · answered Nov 05 '13 at 18:11

4

Whoever named your columns needs their knuckles to meet a swiftly moving ruler.

;WITH cte AS 
(
  SELECT IJLOC, IJITEM, IJDATE, IJLCGT,
    rn = ROW_NUMBER() OVER (PARTITION BY IJITEM, IJLOC ORDER BY IJDATE DESC)
  FROM dbo.IJ
)
SELECT IJLOC, IJITEM, IJDATE, IJLCGT
FROM cte WHERE rn = 1;

answered Nov 05 '13 at 18:11

Aaron Bertrand

272,866
37
466
490

I agree. I have no power over how the fields are named. – tsqln00b Nov 05 '13 at 18:13
Now I need to add fields from another table name IB to this data. The fields would be QOO, QOH, QCM and AVG. Where in this code would I do my JOIN to that table? – tsqln00b Nov 05 '13 at 18:40
@tsqln00b please ask a new question. – Aaron Bertrand Nov 05 '13 at 18:41
This solution does not solve for the time issue. If there were multiple adjustments on the same date, I am not getting the proper data back for the field IJRBAL. – tsqln00b Nov 05 '13 at 19:23
@tsqln00b well, since I don't even see a column named `IJRBAL` in the question, I don't know how I can help. Perhaps you should include the real columns in your question, and more sample data and desired results, instead of trying to change the requirements. Again. – Aaron Bertrand Nov 05 '13 at 19:24
I figured it out. I apologize. I'm trying to not get in trouble by giving away too much of my company's info. I added the IJLCGT to the code...rn = ROW_NUMBER() OVER (PARTITION BY IJITEM, IJLOC ORDER BY IJDATE DESC, IJLCGT DESC) – tsqln00b Nov 05 '13 at 19:30
+1 for pointing out your "column" names are garbage. You should be able to tell what's in a table just by looking an the table name/column name. I have no clue what the hell `IJRBAL` means. Second fields are a place that you grow corn. – Zane Nov 05 '13 at 19:30
2

@Zane Idiojunctional Rhythm Bus Arbitration Logic... duh – Kermit Nov 05 '13 at 19:34
It means Inventory Running Balance. – tsqln00b Nov 05 '13 at 20:05

Latest row for each combination of item and location

1 Answers1