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
GROUP BY id
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?