title: Retrieve TOP X members of each group using SQL Server 2005
body: <p>I've seen this question asked a couple of times, and I've written my own query, but it's quite slow, and I would be extremely grateful if someone could offer advice on how to speed it up.</p>
  
  <p>In a simplified scenario, I have the following two tables:</p>
  
  <p>Group<br>
  - GroupID (primary key)</p>
  
  <p>Member<br>
  - MemberID (primary key)<br>
  - GroupID  (foreign key)</p>
  
  <p>Let's say, for each GroupID in Group, I want to find the top 2 MemberID values from Member that have that GroupID.</p>
  
  <p>Here's my current query that works, but is painfully slow:</p>
  
  <pre><code>SELECT M.MemberID, M.GroupID
  FROM   Member AS M
  WHERE  M.MemberID in 
          (Select top 2 Member.MemberID
           FROM Member
           Where Member.GroupID = M.GroupID
           ORDER BY Member.MemberID)
  </code></pre>
  
  <p>Say Group has the following rows<br>
  GroupID<br>
  1<br>
  2<br>
  3  </p>
  
  <p>and Member has the following rows<br>
  MemberID, GroupID<br>
  1, 1<br>
  2, 2<br>
  3, 3<br>
  4, 1<br>
  5, 2<br>
  6, 3<br>
  7, 1<br>
  8, 2<br>
  9, 3  </p>
  
  <p>Then my query should return:<br>
  MemberID  GroupID<br>
  1, 1<br>
  2, 2<br>
  3, 3<br>
  4, 1<br>
  5, 2<br>
  6, 3  </p>