Per each record, select also the data with the highest value for the record

Question

Good evening,

I've got the following two tables:

scripts

*----------------------------*
| user | script_id |   name  |
*----------------------------*
| 408  |    1      | script1 |
| 408  |    3      | script2 |
*----------------------------*

script_versions

*--------------------------------*
| id | version |      script     |
*--------------------------------*
|  1 |    1    | print "script1" |
|  1 |    2    | print "script2" |
|  3 |    0    |  print "other1" |
|  3 |    1    |  print "other2" |
*--------------------------------*

The table scripts contains some general information about a script, for example its name and the user ID of its owner. The table script_versions contains the code of all the different versions per each script.

Now what I want to retrieve from the database:

I want to get the newest version (that means the highest version number) of all scripts for a given user.

For example with the given data the query should return:

Wanted result

*-------------------------------------------------*
| id | version |      script     | user |   name  |
*-------------------------------------------------*
|  1 |    2    | print "script2" | 408  | script1 |
|  3 |    1    |  print "other2" | 408  | script2 |
*-------------------------------------------------*

What I've tried so far

This is my query right now. It does not work as it does not always select the newest version (in fact, it currently always selects the oldest) of all the scripts.

Code:

SELECT * 
  FROM scripts 
  LEFT 
  JOIN 
     ( SELECT * 
         FROM script_versions 
       GROUP 
         BY id 
      ORDER 
          BY version DESC 
      ) AS versions 
          ON scripts.script_id = versions.id 
      WHERE scripts.user = ?

Edit:

This question is not a duplicate of this question, because the mentioned is not about ordering / sorting the result (by the newest version).

Answer 1

Try

select scripts.id, v.version, v.script, scripts.user, scripts.name
from scripts s
inner join
(select id, max(version) as version from script_versions group by id) aux
on aux.id = s.script_id
inner join script_versions v on v.id = aux.id and v.version = aux.version
where user = ?