title: Selecting most recent answers efficiently
body: <p>SQL Fiddle: <a href="http://sqlfiddle.com/#!3/9b459/6" rel="nofollow noreferrer">http://sqlfiddle.com/#!3/9b459/6</a></p>
  
  <p>I have a table containing answers to the question "Will you attend this event?". Each user might respond several times and all answers are stored in the table. Normally we're only interested in the latest answer, and I'm trying to construct an efficient query for that. I'm using SQL Server 2008 R2.</p>
  
  <p>Table contents for one event:</p>
  
  <p><img src="https://i.stack.imgur.com/8tUAx.png" alt="Table contents"></p>
  
  <pre><code>Column types: int, int, datetime, bit
  Primary key: (EventId, MemberId, Timestamp)
  </code></pre>
  
  <p>Note that Member 18 first answered No and later Yes, member 20 answered Yes at first and later No, member 11 answered No and later No again. I would like to filter out these member's first answers. Also, there might be more than one answer that should be filtered - a user might for example answer Yes, Yes, No, Yes, No, No, No.</p>
  
  <p>I have tried a few different ideas, and have evaluated them in SQL Server Management Studio by entering all queries, selecting Display Estimated Execution Plan and comparing each query's total cost in percent. Is that a good method for evaluating the performance?</p>
  
  <p>The different queries tested so far:</p>
  
  <pre><code>-----------------------------------------------------------------
  -- Subquery to select Answer (does not include Timestamp)
  -- Cost: 63 %
  -----------------------------------------------------------------
  select distinct a.EventId, a.MemberId,
  (
    select top 1 Answer
    from    Attendees
    where EventId   = a.EventId
    and   MemberId  = a.MemberId
    order by Timestamp desc
  ) as Answer
  from    Attendees a
  where a.EventId = 68
  
  -----------------------------------------------------------------
  -- Where with subquery to find max(Timestamp)
  -- Cost: 13 %
  -----------------------------------------------------------------
  select a.EventId, a.MemberId, a.Timestamp, a.Answer
  from     Attendees a
  where  a.EventId = 68
  and    a.Timestamp =
  (
    select max(Timestamp)
    from     Attendees
    where  EventId  = a.EventId
    and    MemberId = a.MemberId
  )
  order by a.TimeStamp;
  
  -----------------------------------------------------------------
  -- Group by to find max(Timestamp)
  -- Subquery to select Answer matching max(Timestamp)
  -- Cost: 23 %
  -----------------------------------------------------------------
  select a.EventId, a.MemberId, max(a.Timestamp),
  (
    select top 1 Answer
    from    Attendees
    where EventId   = a.EventId
    and   MemberId  = a.MemberId
    and   Timestamp = max(a.Timestamp)
  ) as Answer
  from    Attendees a
  where a.EventId = 68
  group by a.EventId, a.MemberId
  order by max(a.TimeStamp);
  </code></pre>
  
  <p>It would be nice to avoid using a subquery for each member. In the last query I tried using <code>group by</code> but still had to use a subquery for the Answer column. I would really like something like this, but that isn't valid SQL of course:</p>
  
  <pre><code>select a.EventId, a.MemberId, max(a.Timestamp), a.Answer &lt;-- Picked from the line selected by max(a.Timestamp)
  from  Attendees a
  where a.EventId = 68
  group by a.EventId, a.MemberId
  order by max(a.TimeStamp);
  </code></pre>
  
  <p>Any other ideas for an efficient query?</p>
  
  <hr>
  
  <p>EDIT:</p>
  
  <p>Very impressed by SQL Fiddle, I have entered my actual data there now:
  <a href="http://sqlfiddle.com/#!3/9b459/6" rel="nofollow noreferrer">http://sqlfiddle.com/#!3/9b459/6</a></p>