SQLite: How to SELECT "most recent record for each user" from single table with composite key?

You could try this:

select user_id, max(last_updated) as latestfrom recordsgroup by user_id

This should give you the latest record per user. I assume you have an index on user_id and last_updated combined.

In the above query, generally speaking - we are asking the database to group user_id records. If there are more than 1 records for user_id 1, they will all be grouped together. From that recordset, maximum last_updated will be picked for output. Then the next group is sought and the same operation is applied there.

If you have a composite index, sqlite will likely just use the index because the index contains both fields addressed in the query. Indexes are smaller than the table itself, so scanning or seeking is faster.

Well, in true "d'oh!" fashion, right after I ask this question, I find the answer.

For my case, the answer is:

SELECT MAX(Last_Updated),User_ID FROM records GROUP BY User_ID

I was making this more complicated than it needed to be by thinking I needed to use JOINs and stuff. Applying an aggregate function like MAX() is all that's needed to select only those rows whose content matches the function result. That means this statement…

SELECT MAX(Last_Updated),User_ID FROM records

…would therefor return a result set containing only 1 row, the most recent event.

By adding the GROUP BY clause, however, the result set contains a row for each "group" of results, i.e., for each user. My programmer-brain did not understand that GROUP BY is how we say "for each" in SQL. I think I get it now.

Note to self: keep it simple, stupid. :)