title: Find last (first) instance in table but exclude most recent (oldest) date
body: <p>I have a table that reflects a monthly census of a certain population.  Each month on an unpredictable day early in that month, the population is polled.  Any member who existed at that point is included in that month's poll, any member who didn't is not.  </p>
  
  <p>My task is to look through an arbitrary date range and determine which members were added or lost during that time period.  Consider the sample table:</p>
  
  <pre><code>ID | Date
  2  | 1/3/2010
  3  | 1/3/2010
  1  | 2/5/2010
  2  | 2/5/2010
  3  | 2/5/2010
  1  | 3/3/2010
  3  | 3/3/2010
  </code></pre>
  
  <p>In this case, member with ID "1" was added between Jan and Feb, and member with ID 2 was lost between Feb and Mar.</p>
  
  <p>The problem I am having is that if I just poll to try and find the most recent entry, I will capture all the members that were dropped, but also all the members that exist on the last date.  For example, I could run this query:</p>
  
  <pre><code>SELECT
    ID,
    Max(Date)
  FROM
    tableName
  WHERE
    Date BETWEEN '1/1/2010' AND '3/27/2010'
  GROUP BY
    ID
  </code></pre>
  
  <p>This would return:</p>
  
  <pre><code>ID  | Date
  1   | 3/3/2010
  2   | 2/5/2010
  3   | 3/3/2010
  </code></pre>
  
  <p>What I actually want, however, is just:</p>
  
  <pre><code>ID  | Date
  2   | 2/5/2010
  </code></pre>
  
  <p>Of course I can manually filter out the last date, but since the start and end date are parameters I want to generalize that.  One way would be to run sequential queries.  In the first query I'd find the last date, and then use that to filter in the second query.  It would really help, however, if I could wrap this logic into a single query.</p>
  
  <p>I'm also having a related problem when I try to find when a member was first added to the population. In that case I'm using a different type of query:</p>
  
  <pre><code>SELECT
    ID,
    Date
  FROM
    tableName i
  WHERE
    Date BETWEEN '1/1/2010' AND '3/27/2010'
   AND
    NOT EXISTS(
      SELECT
        ID,
        Date
      FROM
        tableName ii
      WHERE
        ii.ID=i.ID
       AND
        ii.Date &lt; i.Date
       AND
        Date BETWEEN '1/1/2010' AND '3/27/2010'
    )
  </code></pre>
  
  <p>This returns:</p>
  
  <pre><code>ID  | Date
  1   | 2/5/2010
  2   | 1/1/2010
  3   | 1/1/2010
  </code></pre>
  
  <p>But what I want is:</p>
  
  <pre><code>ID  | Date
  1   | 2/5/2010
  </code></pre>
  
  <p>I would like to know:
  1. Which approach (the MAX() or the subquery with NOT EXISTS) is more efficient and
  2. How to fix the queries so that they only return the rows I want, excluding the first (last) date.</p>
  
  <p>Thanks!</p>