MYSQL – max(id) pulling correct id but incorrect related data [closed]

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.

My data may consist of :

 id   |  name  | status | sched
 ---------------------------------
 1     wks-1   Canceled  1/1
 2     wks-1    New      2/2

And my query is:

    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

My results are :

 max_id   |  name  | status | sched
 ---------------------------------
  2          wks-1  Canceled 1/1

How can I get it to return the corresponding data from row 2 rather than the id from 2 and data from one?

I’ve also tried this:

   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

But it doesn’t know what the a.name is referring to in the “name = a.name” join condition.

Any help is greatly appreciated.

Answer

Never mind, the e.status and e.sched shoudld be f.status and f.sched. Wasn’t thinking completely. Thanks for consideration regardless.

select max_id, a.name,  f.status, f.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

Advertisement

Answer