title: SQL: Selecting rows contained in a group
body: <p>I'm working on a query for SQL Server and I'm wondering if anyone can give me some tips on selecting individual rows that make up a group (where the group's based on an aggregate function, COUNT in this case)</p>
  
  <p>So, as a simplified example, if I have a table of billings, as below, I want to select all billings for each client where they have 2 or more billings after a certain date.</p>
  
  <pre><code>ClaimID     ClaimDate             ClientName
  101         May 5, 2010           Jim
  102         June 19, 2010         Jim
  103         August 5, 2008        Jim
  104         January 1, 2011       Mary
  105         May 8, 2009           Mary
  106         November 4, 2010      Mary
  107         October 6, 2010       Mary
  108         April 4, 2010         Bob
  109         April 29, 2009        Bob
  110         July 7, 2006          Joe
  </code></pre>
  
  <p>So if I execute</p>
  
  <pre><code>SELECT ClientName, COUNT(ClaimID) FROM Billings
  WHERE ClaimDate &gt; '2010'
  Group By ClientName
  Having COUNT(ClaimID) &gt; 1
  </code></pre>
  
  <p>I'd get:</p>
  
  <pre><code>Jim  2
  Mary    3
  </code></pre>
  
  <p>Which is good, it finds all clients who have 2 or more billings in the time frame, but I want to actually list what those billings are.
  So I want this: </p>
  
  <pre><code>ClaimID ClientName  Count
  101         Jim     2
  102         Jim     2
  104         Mary    3
  106         Mary    3
  107         Mary    3
  </code></pre>
  
  <p>What do you think is the best way to accomplish this?</p>
  
  <p>Thanks.</p>