Left join SQL tables and get value of most recent row

Question

I've an SQL statement that I've been dealing with for a while now.

How do I get the most recent (by date, and then id) weight-value from another table. This is my query so far:

SELECT e.name, e.picture_url, e.id AS exercise_id, w.parent_id, e.video_url,
        w.id, w.comment, w.time, w.reps, w.rest, w.sets, wt.name as wtype,
        w.order_by, mg.name as muscle_group, eq.name as equipment, w_track.*
        FROM workout w
        LEFT JOIN exercises e
        ON e.id = w.exercise_id
        LEFT JOIN workout_types wt
        ON wt.id = e.workout_type_id
        LEFT JOIN muscle_groups mg
        ON e.muscle_group_id = mg.id
        LEFT JOIN equipments eq
        ON eq.id = e.equipment_id

        LEFT JOIN (SELECT workout_id, weight FROM workout_tracking GROUP BY workout_id ORDER BY date DESC, id DESC) w_track
        ON w.id = w_track.workout_id


        WHERE workout_day_id = 395

I want to left join workout_tracking and get the most recent 'weight' value - the row with the newest date and highest id.

Thanks in advance! All help appreciated!

UPDATE When I run the above query I get some random 'weight' number, and not the most recent one.

The 'workout' table consists of: - id (primary key for workout_tracking) - workout_day_id (foreign key for 'workout_day' - reps - rest - sets - etc...

The 'workout_tracking' table consists of: - id - workout_id (workout-table foreign key) - weight - date

Sample data 'workout'-table:

  +---+------------+-------------+
  |id |     sets   |    reps     |
  +---+------------+-------------+
  | 1 |      3     |     10

'workout_tracking'-table:

  +---+------------+-------------+-------------+
  |id | workout_id |    weight   |    date
  +---+------------+-------------+-------------+
  | 1 |      1     |     10        2017-05-22
    2        1           12        2017-05-28
    3        1           14        2017-05-28
    4        1           11        2017-05-28
    5        1          NULL       2017-05-28
    6        1          NULL       2017-05-28

Show us db schema, sample data and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, May 29 '17 at 19:30
I guess your problem is with the `SELECT workout_id, weight FROM workout_tracking GROUP BY workout_id` ?? — Juan Carlos Oropeza, May 29 '17 at 19:31
What is the difference between `workout_id` and `id` in the `workout_tracking` table?...with that info I can help you adding `max` to the query based on the right field. — Hackerman, May 29 '17 at 19:34
`id` is the id of the workout_tracking row, and workout_id is the foreign key for table `workout` — Mathias Lund, May 29 '17 at 19:36
Check this example: https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results — Juan Carlos Oropeza, May 29 '17 at 19:36
Do you really need order by id and date? isnt the latest id the latest date? — Juan Carlos Oropeza, May 29 '17 at 19:37
@JuanCarlosOropeza I guess it will work only by id at first :) — Mathias Lund, May 29 '17 at 19:41
Show us some sample data, only for that `workout_tracking` table — Juan Carlos Oropeza, May 29 '17 at 19:48
So again what weigth you want? Because you order by id and date? Do you have different weitght on same day? Try to add morer data with same day and tell us what result you expect. — Juan Carlos Oropeza, May 29 '17 at 19:59
I've added more data. I want to get the 11 in `weight`, the weight of the most recent date, with the highest id :) — Mathias Lund, May 29 '17 at 20:02

Juan Carlos Oropeza · Accepted Answer · 2017-05-29T20:23:07.360

1

The easy way is using variables:

SQL DEMO

SELECT `id`, `workout_id`, `weight`, `date`,
       @rn := IF( @workout_id = `workout_id`,
                  @rn + 1,
                  IF(@workout_id := `workout_id`, 1, 1)
                ) as rn
FROM workout_tracking     
CROSS JOIN (SELECT @rn := 0, @workout_id := -1) as var
ORDER BY `workout_id`, `date` DESC, `id` DESC

Because you order first for workout_id you can find the latest date and latest id, in this case those will have @rn = 1

Now your final query should be

SELECT w.*, filter.*
FROM workout w
JOIN (SELECT `id`, `workout_id`, `weight`, `date`,
              @rn := IF( @workout_id = `workout_id`,
                         @rn + 1,
                         IF(@workout_id := `workout_id`, 1, 1)
                       ) as rn
      FROM workout_tracking     
      CROSS JOIN (SELECT @rn := 0, @workout_id := -1) as var
      ORDER BY `workout_id`, `date` DESC, `id` DESC
     ) as filter
 ON w.id = filter.`workout_id`
WHERE filter.rn = 1;

For LEFT JOIN try:

LEFT JOIN ( ... ) as filter
  ON w.id = filter.`workout_id`
 AND filter.rn = 1

dont need the where condition.

edited May 29 '17 at 20:23

answered May 29 '17 at 20:07

Juan Carlos Oropeza

47,252
12
78
118

Thanks for you answer, but sequel pro can't run this query - it says "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@rn + 1, IF(@workout_id := `workout_id`, 1, 1) ' at line 5" – Mathias Lund May 29 '17 at 20:10
I miss a comma, try again – Juan Carlos Oropeza May 29 '17 at 20:12
Check the Live Demo example. – Juan Carlos Oropeza May 29 '17 at 20:19
wow Juan well done, there is only one more thing - now it doesn't include the exercises that don't have any workout_tracking rows associated with them. I've tried to do LEFT JOIN instead of just regular JOIN in your query – Mathias Lund May 29 '17 at 20:19
It still doesn't include the exercises without workout_tracking rows, when I do a left join. – Mathias Lund May 29 '17 at 20:21
Awesome Juan! One last thing, can you also do so if in case the weight of most recent row is NULL, we pick the weight of the most recent row that is not NULL :)? – Mathias Lund May 29 '17 at 20:29
mmmm I dont see how is that possible, show me an example. You can edit the rextester demo with additional data, Fork it and send me the link. – Juan Carlos Oropeza May 29 '17 at 20:30
Do you mean `weight` can be null? In that case just filter `WHERE weight IS NOT NULL` in the inner query. – Juan Carlos Oropeza May 29 '17 at 20:32

score 0 · Answer 2 · answered May 29 '17 at 19:45

0

LEFT JOIN (SELECT MAX(workout_id) AS workout_id, weight FROM workout_tracking GROUP BY workout_id ORDER BY date DESC, id DESC) w_track ON w.id = w_track.workout_id

answered May 29 '17 at 19:45

zarif khan

81
6

Hi zarif, I tried your query, but it still gives me some random weight, which is not the most recent – Mathias Lund May 29 '17 at 19:49

Left join SQL tables and get value of most recent row

2 Answers2