SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    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

  2. #2
    SitePoint Member
    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

  3. #3
    Hi there! Owen's Avatar
    Join Date
    Jan 2000
    Location
    CA
    Posts
    1,165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Member
    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

  5. #5
    SitePoint Zealot
    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;
    if you can specify which columns you want returned

  6. #6
    SitePoint Member
    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?

    Chris
    Last edited by cpine; May 4, 2002 at 15:09.

  7. #7
    SitePoint Zealot
    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

  8. #8
    SitePoint Member
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •