Need help using local variables to subtract previous row value having several groups

Question

I have 3 tables as follows:

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int(11)      | NO   | PRI | 0       |       |
| full_name | varchar(200) | YES  |     | NULL    |       |
| gender    | varchar(1)   | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| actor_id | int(11) | YES  | MUL | NULL    |       |
| movie_id | int(11) | YES  | MUL | NULL    |       |
| salary   | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | 0       |       |
| title | varchar(100) | YES  |     | NULL    |       |
| year  | int(11)      | YES  |     | NULL    |       |
| genre | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

I'm trying to figure out which actor has the longest time between movies.

I'm trying to use local variables to reference the previous row value in the case where the actor was the same (or else it defaults to 0). However for some reason the @previousName variable returns a random list of names.

Here is the code:

SELECT 
    a.full_name, 
    m.year,
    m.title,
    @PreviousName,
    @PreviousYear,
    if(@PreviousName = a.full_name, m.year - @PreviousYear, 0) AS Delta,
    @PreviousName := a.full_name,
    @PreviousYear := m.year 
FROM
    actors AS a
        INNER JOIN
    cast AS c ON a.id = c.actor_id
        INNER JOIN
    movies AS m ON c.movie_id = m.id,
    (SELECT @PreviousName := null, @PreviousYear := 999) as SQLVars
ORDER BY full_name;

And here is what I get: Picture of result table

Note I am using mySQL V5.7 so window functions are not an option.

What if an actor has two movies in the same year? – Gordon Linoff Apr 07 '18 at 18:15 — Gordon Linoff, Apr 07 '18 at 18:15

Gordon Linoff · Answer 1 · 2018-04-09T01:53:56.210

0

You can do this with a correlated subquery:

select mc.*, (year - prev_year) as diff
from (select c.*, m.year,
             (select m2.year
              from movies m2 join
                   cast c2
                   on c2.movie_id = m2.id
              where c2.actor_id = c.actor_id and
                    m2.year < m.year
              order by m2.year desc
              limit 1
             ) prev_year
      from movies m join
           cast c
           on c.movie_id = m.id
     ) mc
order by diff desc;

This assumes that an actor is not in two movies in the same year. If you had a release date or something, that would be more effective for ordering the times.

edited Apr 09 '18 at 01:53

answered Apr 07 '18 at 18:16

Gordon Linoff

1,242,037
58
646
786

Your code doesn't run for me. It'd be good if it did, but I'd still like to learn where I went wrong in my method. – Idodo Apr 07 '18 at 19:07
@ldodo . . . "doesn't run" is rather vague and doesn't help me or anyone else spot the problem. – Gordon Linoff Apr 08 '18 at 02:09
You're right, my apologies. The error I get is "Unknown column 'year' in field list" - on the first line. In addition, I don't understand line 7, how does SQL know what "c.actor_id is? as "c" is not included in the FROM clause. – Idodo Apr 08 '18 at 23:06
@Idodo . . . That oversight is easily fixed. – Gordon Linoff Apr 09 '18 at 01:54

Need help using local variables to subtract previous row value having several groups

1 Answers1