title: SQLite: How to SELECT "most recent record for each user" from single table with composite key?
body: <p>I'm not a database guru and feel like I'm missing some core SQL knowledge to grok a solution to this problem. Here's the situation as briefly as I can explain it.</p>
  
  <p><strong>Context:</strong></p>
  
  <p>I have a SQLite database table that contains timestamped user event records. The records can be uniquely identified by the combination of timestamp and user ID (i.e., when the event took place and who the event is about). I understand this situation is called a "composite primary key." The table looks something like this (with a bunch of other columns removed, of course):</p>
  
  <pre><code>sqlite&gt; select Last_Updated,User_ID from records limit 4;
  
  Last_Updated   User_ID
  -------------  --------
  1434003858430  1   
  1433882146115  3   
  1433882837088  3   
  1433964103500  2   
  </code></pre>
  
  <p><strong>Question:</strong> How do I <code>SELECT</code> a result set containing only the <em>most recent</em> record for <em>each</em> user?</p>
  
  <p>Given the above example, what I'd like to get back is a table that looks like this:</p>
  
  <pre><code>Last_Updated   User_ID
  -------------  --------
  1434003858430  1   
  1433882837088  3   
  1433964103500  2   
  </code></pre>
  
  <p>(Note that the result set only includes user <code>3</code>'s most recent record.)</p>
  
  <p>In reality, I have approximately 2.5 million rows in this table.</p>
  
  <p><strong>Bonus:</strong> I've been reading answers about JOINs, de-dupe procedures, and a bunch more, and I've been googling for tutorials/articles in the hopes that I would find what I'm missing. I have extensive programming background so I could de-dupe this dataset in procedural code like I've done a hundred times before, but I'm tired of writing scripts to do what I believe should be possible in SQL. That's what it's for, right?</p>
  
  <p>So, what do you think is missing from my understand of SQL, conceptually, that I need in order to understand why the solution you've provided to my question actually works? (A reference to a good article that actually explains the theory behind the practice would suffice.) I want to know WHY the solution actually works, not just that it does.</p>
  
  <p>Many thanks for your time!</p>