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).

@Xatenev I'm implementing the query within php, but I can remove it if you want :) — dv02, May 20 '17 at 22:42
Why don't you simply reverse the ORDER BY clause if its working except for the order being wrong. — Xatenev, May 20 '17 at 22:42
@Xatenev That doesn't change anything, I think that's because of the `GROUP BY` function — dv02, May 20 '17 at 22:44
Your comments in the edit are incorrect — this question is exactly a duplicate because the versions are what you want the maximum of. — Jonathan Leffler, May 21 '17 at 05:32

score 1 · Accepted Answer · edited May 20 '17 at 23:24

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 = ?

edited May 20 '17 at 23:24

dv02

333
1
6
17

answered May 20 '17 at 22:57

Fran Cerezo

940
3
8
19

Thank you, works perfectly, apart from a little spelling mistake. Could you maybe accept my edit? :) – dv02 May 20 '17 at 23:02
Ups! :) Accepted – Fran Cerezo May 20 '17 at 23:25