DirtyDev DirtyDev -4 years ago 73
MySQL Question

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

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 Source

Try

select s.id, v.version, v.script, s.user, s.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 = aix.id and v.version = aux.version;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download