Q:

SQLITE: Find state of multiple tables after 1 commit

I have the following 3 tables:
FirstNames      
Name    NameID  CommitID
Sam     1       1
Dave    2       1
James   3       1
Andrew  2       2

SecondNames     
Name     NameID CommitID
Stevens  1      1
Jones    2      1
Michaels 3      1
Anns     2      2
K        1      2

NameID
NameID
1
2
3

The NameID field from the NameID table is a unique field which is generated the first time a new name is saved to the database. A name consists of a second and first name (stored in seperate tables) where the NameID links the first name to the second name. 
The CommitID is an ID associated with a particular update to the names. The update can be to just the firstname, secondname or both the firstname and the secondname at the same time. If updating multiples names at the same time then the CommitID will be the same for this group of updates.
My aim is to write a query to create a 4th table that tells me what the state of the "edited" names were at a particular commit. As an example, you can see from here that there been 2 comits (commitid 1 & 2). My query would say something like:
Get me the first and second name for all names that had either the first or second name edited by commit 2. This would return the following table:
EditedNameID    FirstName   SecondName  CommitID
1               Sam         K           2
2               Andrew      Anns        2

The important point to note (and the point where I am failing) is that "Sam K" takes his firstname from commit 1 but his secondname from commit 2.
I have struggled on this all morning and not getting to the right answer so wondering has anyone a solution for this? Let me know if my explanation is unclear or if perhaps the requirement would involve me adjusting the schema.

A:

You don't want to see any row with a larger CommitID. So just filter them out with WHERE before searching for the largest CommitID:
SELECT NameID AS EditedNameID,
       (SELECT Name
        FROM FirstNames
        WHERE NameID = NameID.NameID
          AND CommitID <= ?
        ORDER BY CommitID DESC
        LIMIT 1
       ) AS FirstName,
       (SELECT Name
        FROM LastNames
        WHERE NameID = NameID.NameID
          AND CommitID <= ?
        ORDER BY CommitID DESC
        LIMIT 1
       ) AS LastName,
       ? AS CommitID
FROM NameID
WHERE NameID IN (SELECT NameID FROM FirstNames WHERE CommitID = ?)
   OR NameID IN (SELECT NameID FROM LastNames  WHERE CommitID = ?);

