CODEWITHSUNDEEP
sqlsqlite
SSS
SSS

Reputation: 791

Sqlite - get the most recent record per table

I am keeping track of people changing their names. I have three tables:

TableOne
id
1
2

TableTwo            
Date        id  FirstName   NameID
01/02/2016  1   TestOne     1
01/03/2017  2   TestTwo     2
01/04/2015  3   TestThree   1

TableThree          
Date        id  SecondName  NameID
01/01/2016  1   TestThree   1
01/01/2018  2   TestFour    1
01/10/2014  3   TestFive    2

The NameIDs in tables 2 & 3 map to the id in table 1. Every time a person changes their first name I add a record to table 2 and every time a person changes their second name I add a record to table 3. I keep list of all their historical names.

My aim is to use a SELECT query to pull back what each person's name today is i.e. I need to SELECT the most recent pair of names for each unique id in table 1.

My end table should look like this:

NameID  FirstName   SecondName
1       TestOne     TestFour
2       TestTwo     TestFive

I can get the most recent firstnames and secondnames as follows:

SELECT FirstName, MAX(Date) FROM TableTwo GROUP BY NameID;
SELECT SecondName, MAX(Date) FROM TableOne GROUP BY NameID;

But how can I put these 2 queries together? (Sorry if easy question - I am quite new to this and please let me know if anything unclear at all - thanks in advance!)

Upvotes: 2

Views: 786

Answers (1)

CL.
CL.

Reputation: 180280

Use correlated subqueries to lookup the corresponding names:

SELECT id,
       (SELECT FirstName
        FROM TableTwo
        WHERE NameID = TableOne.id
        ORDER BY Date DESC
        LIMIT 1
       ) AS FirstName,
       ...
FROM TableOne;

Upvotes: 2

Related Questions