Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    16

    Unanswered: Trying to get a single record result [Solved]

    Hey Gang -

    I am trying to get single value result from a table that has multiple records. I think if I explain the data, you'll see what I'm trying to get.

    Code:
    +--------+-----------+-------+
    | UnitID | InvoiceID | NUDID |
    +--------+-----------+-------+
    |      2 |       342 |     1 |
    |      2 |       861 |     2 |
    |      2 |      4320 |     2 |
    |      2 |      1153 |     2 |
    |      2 |       850 |     2 |
    |      4 |       230 |     1 |
    |      6 |      4466 |     7 |
    |      6 |       693 |     1 |
    |      9 |       624 |     1 |
    |      9 |      2720 |     7 |
    |      9 |      1288 |     2 |
    |      9 |      1287 |     2 |
    |     12 |      3481 |     2 |
    |     12 |      1237 |     5 |
    |     13 |       863 |     2 |
    |     13 |       660 |     2 |
    |     13 |      3546 |     2 |
    |     13 |       852 |     2 |
    |     13 |       855 |     2 |
    |     13 |       857 |     2 |
    |     13 |       858 |     2 |
    |     13 |       860 |     2 |
    |     14 |      4076 |     4 |
    |     15 |      3478 |     1 |
    +--------+-----------+-------+
    What I'm trying to get are individual unit id's with the nudid corresponding to the highest invoiceid.

    I was using a simple query :
    Code:
    select UnitID, max( InvoiceID ) as InvID, NUDID from table group by 1
    RESULTS:
    +--------+-------+-------+
    | UnitID | InvID | NUDID |
    +--------+-------+-------+
    |      2 |  4320 |     1 |
    |      4 |   230 |     1 |
    |      6 |  4466 |     1 |
    |      9 |  2720 |     1 |
    |     12 |  3481 |     5 |
    |     13 |  3546 |     2 |
    |     14 |  4076 |     4 |
    |     15 |  3478 |     1 |
    +--------+-------+-------+
    The problem with that is that it doesn't give me the NUDID for the line associated with the max( InvoiceID ). It just gives me the value for NUDID associated with the first entry in the table.

    So, for example, the row result returning the UnitID of 2 should have InvoiceID of 4320 and NUDID of 2 ( since that is the NUDID of the record where InvoiceID = 4320 ).

    Let me know what you think on this...
    Thanks!
    Last edited by dockraz; 08-28-12 at 15:52. Reason: Solved - so editing title!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider using Select TOP with an appropriate sort order
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2010
    Posts
    16
    TOP ( or MySQL Limit ) really won't help - i'm not trying to limit the number of records I get - i'm already getting 1 record per unit id - i'm just not getting the correct NUDID when I get the most recent InvID.

  4. #4
    Join Date
    Mar 2004
    Posts
    480
    TOP does not work with mysql.

    See: Common MySQL Queries for a solution to your problem.

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I am making an assumption here that you are looking for the max invoice id for each unitID. So if we simply get the max(invoices) for unitID and get all respective lines based on this we should get what you are looking for:

    Code:
    SELECT UnitID, InvoiceID, NUDID
    FROM table t1
    INNER JOIN (SELECT max( InvoiceID ) as InvoiceID from table group by UnitID) as t2 ON (t1.InvID = t2.InvID);
    
    +--------+-------+-------+
    | UnitID | InvID | NUDID |
    +--------+-------+-------+
    |      2 |  4320 |     2 |
    |      4 |   230 |     1 |
    |      6 |  4466 |     7 |
    |      9 |  2720 |     7 |
    |     12 |  3481 |     2 |
    |     13 |  3546 |     2 |
    |     14 |  4076 |     4 |
    |     15 |  3478 |     1 |
    +--------+-------+-------+
    8 rows in set (0.00 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Mar 2010
    Posts
    16
    quelphdad -

    Your link provided the exact solution I was looking for - Thank You!!!!!

    Ronan -

    Your answer was almost exactly what the link quelphdad gave said...

    Thank you as well!!!
    Last edited by dockraz; 08-28-12 at 15:56. Reason: modified

Posting Permissions

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