B.Sverediuk B.Sverediuk - 4 months ago 8
SQL Question

Select top 1 row with aggregate function

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

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