title: Replicating the MS Access "First" function in SQL Server Query
body: <p>I am very new to SQL Server so I apologize now.</p>
  
  <p>I have a table and I want to GroupBy <code>field1</code> and return the <code>field2</code> record which has the highest associated count in the <code>counted</code> field. I would usually do this in MS ACCESS with 2 queries, the 1st query to return the data in descending order and the second query to pick the 1st record using the First() function, like so:-</p>
  
  <p>Query 1</p>
  
  <pre><code>SELECT t.field1, t.field2, Count(t.counted) AS count1
  FROM Table1 AS t
  WHERE (((t.counted)=2))
  GROUP BY t.field1, t.field2
  ORDER BY t.field1, Count(t.counted) DESC;
  </code></pre>
  
  <p>Query 2 (based off query 1 above)</p>
  
  <pre><code>SELECT q.field1, First(q.field2) AS firstoffield2
  FROM q
  GROUP BY q.field1;
  </code></pre>
  
  <p><a href="https://docs.google.com/spreadsheet/ccc?key=0Ana40VqkvVtRdHhVUlVjRnBFN0c4VnFoOFNaZ3dSQnc" rel="nofollow">SOURCE DATA and query results I am looking for</a></p>
  
  <p>I am having great difficulty trying to accomplish the same results as the above in a SQL Server 2008 query. Can anybody help ? (please provide the precise SQL I will need to use).</p>
  
  <p>Here's a subset of the data and example of the results:-</p>
  
  <p>Table1</p>
  
  <pre><code>field1 ¦ field2 ¦ counted
  10     ¦ 20     ¦ 2
  10     ¦ 30     ¦ 2
  10     ¦ 20     ¦ 2
  20     ¦ 30     ¦ 0
  20     ¦ 40     ¦ 0
  20     ¦ 50     ¦ 1
  20     ¦ 50     ¦ 2
  20     ¦ 60     ¦ 1
  </code></pre>
  
  <p>Query1 results (groups by field1, counts where "counted" field record is "2")</p>
  
  <pre><code>field1 ¦ field2 ¦ count1
  10     ¦ 20     ¦ 2
  10     ¦ 30     ¦ 1
  20     ¦ 50     ¦ 1
  </code></pre>
  
  <p>Query 2 resuls (the output I want to get from SQL)</p>
  
  <pre><code>field1 ¦ firstoffield2
  10     ¦ 20
  20     ¦ 50
  </code></pre>
  
  <p>I hope that helps a bit, thanks guys.</p>