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.

Obituary

There isn't a direct equivalent to the FIRST function in T-SQL. From your description it seems that using the MAX statement might do the trick but it would be helpful if we had some sample data and if you could highlight what you hope will be returned by the process. This way we can take a look at your desired results and try to come up with something that will do the trick. – Delibes

Ok, thanks Tim. I will post data. – Obituary

Link to Source Data – Obituary

@FranzSigmar the link to the source data is failing, can you edit your post and place the data here? – Sigmund

Is there a way of uploading files ? – Obituary

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.

Neustria

Change ROW_NUMBER() to DENSE_RANK() to return all rows in the event of a tie. (This answer currently picks just one even in the event of a tie.) – Neustria

Superb, I can't thank you enough Dems, if you were here I'd hug you or ask my wife to - whichever you prefer :-) – Obituary

@FranzSigmar - you're welcome :) Feel free to vote for answers that helped, and accept the best one for you personally. (I have nothing against you or your wife, but I'd prefer a beer thanks...) – Neustria

Superb, I can't thank you enough Dems, I've just checked all the output and it's spot on. I had to make one small and removed the ORDER BY command :- ORDER BY t.field1, Count(t.counted) DESC; I'm happy for any row to be picked in the event of a tie, it won't happen often and doesn't matter in any case. I have one last question, how do I get this WITH statement into a select query that I can use in a Stored Procedure ? Thanks again Dems, I really appreciate you help. – Obituary

Look up Common Table Expressions (CTE). You put them before your main statement as if you are creating separate sub-queries or inline views. Then you just use them like any other view/table. Good luck. – Neustria

Thanks Dems, I've ticked your solution. Keep a note of those beers I owe you :-) – Obituary

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

Delibes

Use CTEs to make it a little neater. Also, if there is a tie for first, this approach returns all the ties row, where as FIRST() would not. This may be an advantage, or not, depending on what the OP needs. But the OP has only asked for FIRST() to be replicated. – Neustria

@Dems Thanks for the tip. I like your approach! – Delibes

Hi Tim, thanks very much this also worked for me but Dems just beat you to it. For anybody else out there, Tim's query also returns ties, Dems query does not so I guess you can simple cut and paste the one you need. – Obituary

Answer 3

I was looking for the same thing and didn't like the complicated solutions, so I kept looking and found this:

https://www.sqlservercentral.com/forums/topic/t-sql-equivalent-to-ms-access-first-function

Use "Top 1"

Select Top 1 SomeColumn From SomeTable Order By

This is really nice because you have added functionality of a complex ORDER BY to get exactly which one you want If there are multiple firsts

Berm