MS Access First Feature Replication in SQL Server Query

Question

I am very new to SQL Server, so I'm sorry.

I have a table and I want GroupBy field1 and return the record field2 that has the largest associated counter in the counted field. I usually do this in MS ACCESS with 2 queries, the first query to return data in descending order and the second query to select the first record using the First () function, for example: -

Request 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; 

Request 2 (based on request 1 above)

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

SOURCE DATA and the query results I'm looking for

I am having great difficulty trying to achieve the same results as above in a SQL Server 2008 query. Can anyone help? (please specify the exact SQL that I will need to use).

Here is a subset of the data and an example of the results: -

Table 1

 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 the "counted" record of field "2")

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

Query 2 resuls (the output I want from SQL)

 field1 ¦ firstoffield2 10 ¦ 20 20 ¦ 50 

I hope this helps, 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 is not the most elegant query I've ever written, but what about 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