Select top 1 row with aggregate function
<p>I have data in table like this:
<code>UserData</code> table: </p>

<pre><code>|ID| Name  | TeamName |
| 1| Peter | Alpha    |
| 1| Peter | Beta     |
| 1| Peter | Gamma    |
| 2| Mary  | Gamma    |
| 2| Mary  | Omega    |
| 3| John  | Kappa    |
| 3| John  | Delta    |
</code></pre>

<p>Combinations of Name and TeamName are always unique. I need for each unique ID and Name get the top 1 TeamName and number of Team relations, like this:</p>

<pre><code>table #FinalTable
|ID| Name  | TeamName | NumberOfRelations |
| 1| Peter | Alpha    | 3                 |
| 2| Mary  | Gamma    | 2                 |
| 3| John  | Kappa    | 2                 |
</code></pre>

<p>Question - is there a way of doing this in one query, or do I have to use temporary tables for selection top 1 team and for counting number of relations and then select data indo separate final table?
I tried something like this:</p>

<pre><code>;WITH cte AS
(
   SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TeamName Asc) AS rn
   FROM UserData
)
SELECT * into #tempTable1
FROM cte
WHERE rn = 1
</code></pre>

<p>and this:</p>

<pre><code>insert into #tempTable2 (ID, Name, NumberOfRelations)
select ID, Name, count(*) as NumberOfRelations
from UserData
group by ID, Name
</code></pre>

<p>...and then selecting data from two temp tables.
I wonder if there's more simple way of doing it.</p>