Selecting the maximum value for each ID on mysql 3.23

Question

The whole issue is due to the fact that sub queries in a IN clause doesn't seem to work on mysql 3.23.

Here's my table structure for table1:

|**idTable1**|**strDescription**|
|1           |blablabla         |
|2           |blablabla         |

Here's table2:

|**idTable2**|**idTable1**|**intVersion**|
|1           |1           |1             |
|2           |1           |8             |
|3           |2           |2             |
|4           |2           |3             |

What I'm looking to achieve:

Join both tables only to get the description but I only want 1 record of each row that has the biggest version like this:

|**idTable1**|**strDescription**|**idTable2**|**idTable1**|**intVersion**|
|1           |blablabla         |2           |1           |8             |
|2           |blablabla         |4           |2           |3             |

Here's what I came up with:

SELECT table1.idTable1, strDescription, intVersion FROM table1 
INNER JOIN table2 ON table1.idTable1 = table2.idTable1 
WHERE (table1.idTable1, intVersion)
IN (SELECT idTable1, MAX(intVersion) FROM table2 GROUP BY idTable1)

I've tested this query on 2 different versions of mysql. The one I need this to work on is 3.23 and the one where this query works is 5.5.

Is there a way to achieve this on 3.23?

score 0 · Answer 1 · answered Apr 17 '13 at 17:11

I couldn't test this query, but you could try to substitute IN clause with a JOIN:

SELECT
  table1.idTable1, table1.strDescription, table2.intVersion
FROM
  table1 INNER JOIN table2
  ON table1.idTable1 = table2.idTable1 INNER JOIN
  (SELECT idTable1, MAX(intVersion) maxVersion FROM table2 GROUP BY idTable1) mx
  ON table1.idTable1=mx.idTable1 AND table2.intVersion=mx.maxVersion

score 0 · Answer 2 · answered Apr 17 '13 at 17:12

perhaps using a join it will work (untested I don't have access to 3.23.) theory: get max intVersions from Table2 grouped by IDtable1 then join this to the results which would filter out non-max values due to join criteria.

Select T1.IDTable1, T1.strDescription, T2.IDtable2, T2.idTable1, T2.intVersion
FROM Table1 T1 
INNER JOIN table2 T2 on T1.idTable1=T2.IDtable1
INNER JOIN table3 T3 on T3.idtable1 = T1.IDtable1 and T3.Intversion = t2.intVersion
(Select max(intVersion) intVersion, idtable2, idtable1 from table2 group by IDtable1) T3
GROUP BY IDtable1, strDescription, IDtable2, IdTable1

Selecting the maximum value for each ID on mysql 3.23

2 Answers2