CODEWITHSUNDEEP
sqlsql-servert-sql
BohdanZPM
BohdanZPM

Reputation: 755

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.

Upvotes: 3

Views: 6344

Answers (3)

Rich Linnell
Rich Linnell

Reputation: 993

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

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28900

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

SQL tables represent unordered sets. There is no first team name, unless a column specifies the ordering. You don't seem to have such a column.

If you had such a column:

WITH cte AS (
      SELECT ud.*,
             ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ??) as seqnum,
             COUNT(*) OVER (PARTITION BY ID) as cnt
      FROM UserData ud
     )
SELECT cte.*
FROM cte
WHERE seqnum = 1;

Note the ??. This is to specify the ordering for getting the team name. Depending on the database, you can use NULL or (SELECT NULL) to get an arbitrary team name.

Upvotes: 1

Related Questions