title: postgresql: selecting on the basis of the latest pivot row
body: <p>Given a simple schema:</p>
  
  <pre><code>users
  -----
  - id&lt;int&gt;
  - name&lt;string&gt;
  
  organisations
  -------------
  - id&lt;int&gt;
  - name&lt;string&gt;
  
  organisations_users
  -------------------
  - id&lt;int&gt;
  - userId&lt;int&gt;
  - organisationId&lt;int&gt;
  - joinedAt&lt;date&gt;
  </code></pre>
  
  <p>The N:N relation between users and organisations is tracking the organisational history of an individual user.</p>
  
  <p>How do I get all the users who are <em>currently</em> at organisation X? This is basically the same as sorting the pivot table by <code>joinedAt</code>, then only getting the latest <code>organisationId</code> for each user, and then joining with the users who match. I've thought of various ways of collecting all the information I need but the only way I can think of doing this is <code>filter</code>ing in the application code rather than in the SQL query itself (but I'm sure there's a way of doing it).</p>
  
  <h2>sample data</h2>
  
  <pre><code>insert into users (id, name) values
      (1, 'Test User One'),
      (2, 'Test User Two'),
      (3, 'Test User Three');
  
  insert into organisations (id, name) values
      (1, 'Test Org One'),
      (2, 'Test Org Two'),
      (3, 'Test Org Three');
  
  insert into organisations_users ("userId", "organisationId", "joinedAt") values
      (1, 1, '2015-01-01'),
      (1, 2, '2016-01-01'),
      (2, 2, '2015-01-01'),
      (2, 3, '2016-01-01'),
      (3, 3, '2015-01-01'),
      (3, 2, '2016-01-01');
  </code></pre>
  
  <p>At this point, users [1, 3] are in organisation 2 and user 2 is in organisation 3. No-one is in organisation 1.</p>
  
  <h2>expected result</h2>
  
  <p>if I were to try to get everyone in organisation 2 I would obtain users 1 and 3 (with their <code>joinedAt</code> dates):</p>
  
  <pre><code>id | name            | organisationId | joinedAt
  --------------------------------------------------
  1  | Test User One   | 2              | 2016-01-01
  3  | Test User Three | 2              | 2016-01-01
  </code></pre>
  
  <p>I would <em>not</em> get user 2, because although this user was in organisation 2 initially, the user joined organisation 3 subsequently.</p>