Query:

SELECT
    ID, Name, Component, Version 
FROM
    modules 
WHERE 
    modules.ValidTo > NOW() AND modules.ValidFrom < Now();

returns:

Desired data (group by Component but return record with highest Version):

I have tried this query

SELECT
    modules.ID, modules.Name, modules.ComponentIdentifier,
    EXEName, MAX(Version)
FROM
    modules
WHERE 
    modules.ValidTo > NOW() AND modules.ValidFrom < Now()
GROUP BY
    modules.ComponentIdentifier;

But it returns the wrong ID. I would need the ID 17 not 15.

How to solve this?

CodePudding user response：

You are using group by in improper way .. normally the select for not aggregated column not mention in group by is not allowed . in some db this allowed (mysql versione <5.7 or with ONLY_FULL_GROUP_BY disabled) but produce impredictable result
then if you can't add aggregation function to the column not mendtiond in group by
you need subquery for filter the correct value

select modules.ID, modules.Name, modules.ComponentIdentifier,
    EXEName, a.max_ver 
from  (
    SELECT
    modules.ComponentIdentifier,
     MAX(Version) max_ver
    FROM   modules
    WHERE  modules.ValidTo > NOW() AND modules.ValidFrom < Now()
    GROUP BY  modules.ComponentIdentifie
    ) a 
INNER JOIN  modules on modules.ComponentIdentifier = a.ComponentIdentifier 
   and a.max_ver= modules.Version