Skip to content
Advertisement

Group by row but give highest value of other row

Query:

returns:

ID Name Component Version
12 TC1 a 1.0.0
13 TC2 b 1.0.0
15 TC3 c 1.2.0
17 TC3 c 1.2.5

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

ID Name Component Version
12 TC1 a 1.0.0
13 TC2 b 1.0.0
17 TC3 c 1.2.5

I have tried this query

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

ID Name Component Version
12 TC1 a 1.0.0
13 TC2 b 1.0.0
15 TC3 c 1.2.5

How to solve this?

Advertisement

Answer

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

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement