Can't figure out correct SQL query for historical table records

I'm trying for hours to find out the correct SQL query to select the latest historcal record from a table (in MySQL).

In my application I'd like to keep a history of every data modification. So my idea was, instead to make an UPDATE to an existing record, I'd rather make an INSERT of a new record. Additionally there is a revision counter, which gets increased with each record modification.

This is my table:

uid  rid  created_by  deleted  revision  username  password  admin
 1    1      0           0        0      stefan    abcdefg     1
 2    2      1           0        0      maria     bcdefgh     1
 3    3      1           0        0      carl      cdefghi     0
 4    4      1           0        0      SUSANN    ABC123      0
 5    4      1           0        1      SUSANN    123ABC      0
 6    4      1           0        2      SUSANN    123ABC      1
 7    4      1           1        3      SUSANN    123ABC      1

Note the rows with uid 4 to 7 are actually the same record, namely of "SUSANN". Row 4开发者_运维问答 is the initial row. Row 5 modified tha password, row 6 modifies the admin-flag, row 7 modified the deleted-flag.

uid is an auto-incrementor and identifies the row in the table for internal purposes. rid is the actual record-ID.

Now. Selecting the most current revision of a single record could be done this way:

SELECT * FROM table WHERE rid=4 ORDER BY revision DESC LIMIT 1

My problem is selecting a list of all the latest revision of all logins: Based on the sample data the result set should be:

uid  rid  created_by  deleted  revision  username  password  admin
 1    1      0           0        0      stefan    abcdefg     1
 2    2      1           0        0      maria     bcdefgh     1
 3    3      1           0        0      carl      cdefghi     0
 7    4      1           1        3      SUSANN    123ABC      1

Could someone point me in the right direction. I think the right keywords would be sufficient already. From there I could probably figure out a way.

Thanks.

This should work:

SELECT t.* 
FROM table t
JOIN (SELECT rid, MAX(revision) MaxRevision FROM table GROUP BY rid) mt
ON t.rid = mt.rid AND t.revision = mt.MaxRevision