title: Can't figure out correct SQL query for historical table records
body: <p>I'm trying for hours to find out the correct SQL query to select the latest historcal record from a table (in MySQL).</p>
  
  <p>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.</p>
  
  <p>This is my table:</p>
  
  <pre>
  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
  </pre>
  
  <p>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.</p>
  
  <p><strong>uid</strong> is an auto-incrementor and identifies the row in the table for internal purposes. <strong>rid</strong> is the actual record-ID.</p>
  
  <p>Now. Selecting the most current revision of a single record could be done this way:</p>
  
  <p>SELECT * FROM table
  WHERE rid=4
  ORDER BY revision DESC
  LIMIT 1</p>
  
  <p>My problem is selecting a list of all the latest revision of all logins: Based on the sample data the result set should be:</p>
  
  <pre>
  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
  </pre>
  
  <p>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.</p>
  
  <p>Thanks.</p>