dcsimg
Results 1 to 4 of 4

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

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,111

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

    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:
    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.

    Code:
     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:
    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, 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!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,503

    Re: query to get a row with MAX date and one other column

    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, 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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,111

    Re: query to get a row with MAX date and one other column

    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!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,111

    Re: query to get a row with MAX date and one other column

    Yes, that is exactly what I was missing! Thanks again!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width