'versioning' on rows

[cpine]

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

[cpine]

Well, I guess I'm my own best friend today.

I figured it out. Being a total MySQL newbie, I'm rather proud of myself! Here's what I ended up doing:

SELECT t.* FROM MyTable AS t, MyTable AS v
WHERE t.id=v.id
GROUP BY t.id, t.version
HAVING t.version=max(v.version)

whew! I'm sure that's old hat for most of you grizzled MySQLers out there, but it sure got me worked up! (Just so you know, I used only the reference in the back of Kevin Yank's book; you know, the one advertised all over this site. Great book!)

So, if that helps anybody else out there, super. Now, my only question remaining:

Is this going to be super slow with lots of rows in the table? Joining the table with itself seems... scary. Maybe it's just my mathematical background, but going from n to n^2 seems like a whole-lotta-joinin' goin' on. If the primary key is (id, version), will this go quickly?

Chris

[Owen]

Well

SELECT * FROM MyTable
WHERE id=0
ORDER BY version
LIMIT 1;

might do the trick. (I can't remember the syntax for limit. it might be limit(0,1) or limit(1,1))

Owen

[cpine]

Yeah, I thought of that. It would work for retrieving one row, but if I wanted the latest version of every entry (or really every id), I didn't see how it would work.

The statement I gave returned the whole table, which is sometimes nice. For getting just one row, I'm doing just what you wrote. (I think you have to put a DESC at the end of the ORDER BY section, though.)

Chris

[martm]

you could try something like this:

SELECT id, MAX(version) as max_version
FROM myTable   
GROUP BY id;

if you can specify which columns you want returned

[cpine]

Hmmm...

Even if you selected * instead of id, it still wouldn't give the correct row's data (at least not consistently). It just picks the first one in the group, I think.

Anyway, isn't this basically the answer I said didn't work when I asked the question in the first place?

Chris

[martm]

sorry mate,

i was using the example in the MySQL documentation as my basis, but of course you are right that it would not return all columns values correctly.

this might be more useful to you:
MySQL Documentation: The Rows Holding the Group-wise Maximum of a Certain Field


hope this helps

[cpine]

Hey, no problem.

Thanks for the link. I have to say, though, that I think my solution is far more elegant than the one in the manual. Did you look at it? Creating a temporary table?! Yuck! And I can't imagine that being any faster than my way; I'd expect it to be way slower.

I emailed them about it. Maybe they'll update the manual with it.

Chris