title: MYSQL - max(id) pulling correct id but incorrect related data
body: <p>I'm trying to get the latest data from a table based on a join.  I'm having trouble getting it to return anything but the first record, (old data).  The temp_table would just have a listing of workstation names and I need to join it to the workstation data table but only grab the latest data.   </p>
  
  <p>My data may consist of :</p>
  
  <pre><code> id   |  name  | status | sched
   ---------------------------------
   1     wks-1   Canceled  1/1
   2     wks-1    New      2/2
  </code></pre>
  
  <p>And my query is: </p>
  
  <pre><code>    select max_id, a.name,  e.status, e.sched
       from temp_table a
       join (
       select MAX(id) max_id, name, status, sched
        from upgrades
         group by name
         )e on a.name = e.name 
        JOIN upgrades f on f.id = e.max_id
  </code></pre>
  
  <p>My results are : </p>
  
  <pre><code> max_id   |  name  | status | sched
   ---------------------------------
    2          wks-1  Canceled 1/1
  </code></pre>
  
  <p>How can I get it to return the corresponding data from row 2 rather than the id from 2 and data from one?  </p>
  
  <p>I've also tried this: </p>
  
  <pre><code>   select e.max_id, a.name, e.status, e.sched
     from temp_table a
     join (
     select MAX(id) max_id, name, status, sched
     from upgrades where ID =
     (SELECT MAX(id) FROM upgrades where name = a.name GROUP BY name)
     )e on a.mach_name = e.mach_name 
  </code></pre>
  
  <p>But it doesn't know what the a.name is referring to in the "name = a.name" join condition.  </p>
  
  <p>Any help is greatly appreciated.  </p>