[RESOLVED] query to get a row with MAX date and one other column

[MMock]

My problem is the same as this one here https://stackoverflow.com/questions/...axother-column

There were four solutions to that question and I am trying to implement the first, honestly because I don't understand CTE, rank and row_number all that well.

Here is my code:

SELECT inv.item_id,  INV.order_no, date_created
  FROM p21.dbo.p21_view_invoice_line INV
  JOIN (SELECT MAX(t.date_created) AS maxDate
          FROM p21.dbo.p21_view_invoice_line t
         WHERE t.item_id = 'SA_BOZ_161') b ON b.maxDate = INV.date_created
 WHERE INV.item_id = 'SA_BOZ_161'

And it works. There are 7 rows that are item_id SA_BOZ_161 and I get back one with an order_no of 1042272 for the most recent date of 1-15-2018. Once that query returned that row I thought I was done, but I am having a problem with it when I don't specify the item_id. How do I remove the item_id criterion so it will run for all item_id's? I've tried these and they are not right.

 SELECT INV.order_no, date_created
  FROM p21.dbo.p21_view_invoice_line INV
  JOIN (SELECT MAX(t.date_created) AS maxDate
          FROM p21.dbo.p21_view_invoice_line t) b ON b.maxDate = INV.date_created

That gave me 5 rows total. They are duplicates. The order_no is nnnnnn and the date_created is an hour ago, which is obviously the max date in the entire table.

I tried this:

 SELECT inv.item_id,  INV.order_no, date_created
  FROM p21.dbo.p21_view_invoice_line INV
  JOIN (SELECT MAX(t.date_created) AS maxDate, item_id
          FROM p21.dbo.p21_view_invoice_line t group by item_id) b ON b.maxDate = INV.date_created
join dms1.dbo.xtblContracts contr on contr.SABillItem = inv.item_id
where deleted = 0

Because ultimately I will have to join to other tables so I thought I would start throwing them in. xtblContracts has a "deleted" flag so I don't want those records. But when I ran this I got 431 rows (I should get 142), for example the same item_id with a different order_no and different date_created.

My boss asked me to do this, this morning. I told her it wasn't as straightforward as just doing a sql MAX(date_created) function to also get the order_no. She said it was a "nice to have" not necessary to have it, but really am I going to say no I can't do this to her? I feel like I'm almost there!

[techgnome]

 SELECT inv.item_id,  INV.order_no, date_created
  FROM p21.dbo.p21_view_invoice_line INV
  JOIN (SELECT MAX(t.date_created) AS maxDate, item_id
          FROM p21.dbo.p21_view_invoice_line t group by item_id) b ON b.maxDate = INV.date_created AND b.item_id = INV.item_id
join dms1.dbo.xtblContracts contr on contr.SABillItem = inv.item_id
where deleted = 0

You need to join on the date AND the ID...

-tg

[MMock]

Thank you! I totally switched gears and don't want to end the week in a bad mood so I'll try this when I'm done!

[MMock]

Yes, that is exactly what I was missing! Thanks again!