Replicating the MS Access "First" function in SQL Server Query

Question

I am very new to SQL Server so I apologize now.

I have a table and I want to GroupBy field1 and return the field2 record which has the highest associated count in the counted 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:-

Query 1

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;

Query 2 (based off query 1 above)

SELECT q.field1, First(q.field2) AS firstoffield2
FROM q
GROUP BY q.field1;

SOURCE DATA and query results I am looking for

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).

Here's a subset of the data and example of the results:-

Table1

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

Query1 results (groups by field1, counts where "counted" field record is "2")

field1 ¦ field2 ¦ count1
10     ¦ 20     ¦ 2
10     ¦ 30     ¦ 1
20     ¦ 50     ¦ 1

Query 2 resuls (the output I want to get from SQL)

field1 ¦ firstoffield2
10     ¦ 20
20     ¦ 50

I hope that helps a bit, thanks guys.

Answer 1

WITH
  q AS
(
  Put your query one here
)
,
  sequenced AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY field1 ORDER BY count1 DESC) AS sequence_id,
    *
  FROM
    q
)
SELECT
  *
FROM
  sequenced
WHERE
  sequence_id = 1

To change this to LAST() change the order direction in the ROW_NUMBER() function.

Answer 2

This isn't the most elegant query I've ever written, but how about something like this:

SELECT qSource.Field1, qSource.Field2
FROM (SELECT Field1, Field2, COUNT(Counted) AS Count1
    FROM dbo.Table1
    WHERE Counted = 2
    GROUP BY Field1, Field2)qSource
INNER JOIN (SELECT q.Field1,MAX(q.Count1) AS HighestCount
    FROM (SELECT Field1, Field2, COUNT(Counted) AS Count1
        FROM dbo.Table1
        WHERE Counted = 2
        GROUP BY Field1, Field2) q
    GROUP BY q.Field1) qHighest
ON qSource.Field1 = qHighest.Field1
AND qSource.Count1 = qHighest.HighestCount
ORDER BY qSource.Field1