Hey,

What I'm trying to do is keep track of changes to rows. What I've got is a table 'MyTable' with columns 'id' and 'version', these being the primary key.

I'm trying to get the most recent row (highest version number) for a given id with one query. I tried the following:

SELECT * FROM MyTable
WHERE id=0
GROUP BY id
HAVING version=max(version);

This returned 0 rows. (Since I have 2 rows, both with id=0, with version numbers 0 and 1, I was hoping the latter would have been returned.)

If I GROUP BY id, version, it returns both rows.

Does anyone know the correct query? Or is there a totally better way to do this that I didn't think of?

Much thanks,

Chris