Select top 1 row with aggregate function

Question

I have data in table like this: UserData table:

|ID| Name  | TeamName |
| 1| Peter | Alpha    |
| 1| Peter | Beta     |
| 1| Peter | Gamma    |
| 2| Mary  | Gamma    |
| 2| Mary  | Omega    |
| 3| John  | Kappa    |
| 3| John  | Delta    |

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:

table #FinalTable
|ID| Name  | TeamName | NumberOfRelations |
| 1| Peter | Alpha    | 3                 |
| 2| Mary  | Gamma    | 2                 |
| 3| John  | Kappa    | 2                 |

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:

;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

and this:

insert into #tempTable2 (ID, Name, NumberOfRelations)
select ID, Name, count(*) as NumberOfRelations
from UserData
group by ID, Name

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

Answer 1

For SQLserver:

You don't have order by,so i choose one below...

select top 1 with ties id,playen,count(id) over (partition by id,playen) as countt
,temaname
from #temp t1
order by row_number() over (partition by id,playen  order by id,playen,temaname)

Output:

id  playen  countt  temaname
1   Peter   3       Alpha
2   Mary    2      Gamma
3   John    2      Delta

Answer 2

Assuming this is SQL Server try this :

    Select t.ID, t.Name, team.TeamName, count(t.TeamName) countt
from @temp t join
(Select id, TeamName, Row_Number() over (Partition By ID Order By TeamName asc) as rn
from @temp) team on (team.ID = t.ID and team.rn=1)
Group by t.ID, t.Name, team.TeamName