CODEWITHSUNDEEP
sqlpostgresql
Nandakumar V
Nandakumar V

Reputation: 4635

Return first element in array_agg()

I am writing a query to get all players for all teams. Instead of looping in the application, I decided to get the players of all teams in a single query using array_agg(). I have written the query as follows:

SELECT team_id, array_agg(team_name) AS teamname,
       array_agg(player_id||'##'||player_name) AS playerdetails
FROM team
INNER JOIN players ON team_id = player_team
GROUP BY team_id

This query gives me the result as below, in the result set the teamname is being repeated (exactly to the no. of players)

team_id             team_name                                                                   playerdetails
1       {Australia,Australia,Australia,Australia}                       {"5##Glenn Donald McGrath","6##Shane Warne","2##Steve Waugh","1##Adam Gilchrist"}
2       {India,India,India,India}                                       {"8##Kapil Dev","11##Saurav Ganguly","3##Rahul Dravid","9##Sachin Tendulkar"}
3       {"South Africa","South Africa","South Africa","South Africa"}   {"12##Gary Kristen","4##Shaun Pollock","7##Jacques Kallis","10##Alan Donald"}

Is there any way to return the result like this 

team_id             team_name                                                                   playerdetails
1                   Australia                       {"5##Glenn Donald McGrath","6##Shane Warne","2##Steve Waugh","1##Adam Gilchrist"}

I have achieved it using a subquery, but want to know if its possible to write it without the subquery 

SELECT team_id, teamname[1], playerdetails
FROM (
  SELECT team_id, array_agg(team_name) AS teamname,
         array_agg(player_id||'##'||player_name) AS playerdetails
  FROM team
  INNER JOIN players ON team_id = player_team
  GROUP BY team_id) AS tempresult

The sqfiddle is here. And I am using Postgresql 8.4

[EDIT]
I was actually thinking of hack to the GROUP BY limitation column "team.team_status" must appear in the GROUP BY clause or be used in an aggregate function when try to retrieve the a column which was not been specified in group by 

SELECT team_id, array_agg(team_name) AS teamname,
       array_agg(player_id||'##'||player_name) AS playerdetails,
       team_status -- could be replaced by something like array_agg(team_status)[0] or customfunction(team_status)
FROM team
INNER JOIN players ON team_id = player_team
GROUP BY team_id

Upvotes: 37

Views: 46467

Answers (3)

Pravin Solankar
Pravin Solankar

Reputation: 11

SELECT team_id, array_agg(DISTINCT team_name) AS teamname,
       array_agg(player_id||'##'||player_name) AS playerdetails
FROM team
INNER JOIN players ON team_id = player_team
GROUP BY team_id

Upvotes: 1

Nandakumar V
Nandakumar V

Reputation: 4635

It was actually a mistake from my part... the answer to my first question lies in that query itself. I just have to enclose the (array_agg(team_name))[1], earlier I tried it without the brackets. 

SELECT team_id, (array_agg(team_name))[1] AS teamname,
     array_agg(player_id||'##'||player_name) AS playerdetails
FROM team
INNER JOIN players ON team_id = player_team
GROUP BY team_id

Upvotes: 68

Patrick
Patrick

Reputation: 32354

Very simply, do not aggregate the team_name but GROUP BY it:

SELECT team_id, team_name, array_agg(player_id||'##'||player_name) AS playerdetails
FROM team
JOIN players ON team_id = player_team
GROUP BY team_id, team_name;

Upvotes: 5

Related Questions