SitePoint Sponsor |
|
User Tag List
Results 1 to 8 of 8
Thread: 'versioning' on rows
-
May 4, 2002, 11:15 #1
- Join Date
- May 2002
- Posts
- 11
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
'versioning' on rows
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
-
May 4, 2002, 14:36 #2
- Join Date
- May 2002
- Posts
- 11
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
May 4, 2002, 14:39 #3
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
-
May 4, 2002, 14:45 #4
- Join Date
- May 2002
- Posts
- 11
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
May 4, 2002, 15:25 #5
- Join Date
- Feb 2002
- Location
- UK
- Posts
- 146
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
you could try something like this:
Code:SELECT id, MAX(version) as max_version FROM myTable GROUP BY id;
-
May 4, 2002, 16:05 #6
- Join Date
- May 2002
- Posts
- 11
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
ChrisLast edited by cpine; May 4, 2002 at 16:09.
-
May 4, 2002, 18:27 #7
- Join Date
- Feb 2002
- Location
- UK
- Posts
- 146
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
May 4, 2002, 21:03 #8
- Join Date
- May 2002
- Posts
- 11
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
Bookmarks