Return first element in array_agg()

Question

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

I think you can find this useful: stackoverflow.com/questions/3994556/… — user_0
– user_0, Commented Jun 23, 2015 at 8:09
@Patrick First the question is based on some curiosity, I have tried to do it without much success.. and I think using subquery will be less efficient when considering large and complex queries on large datasets — Nandakumar V
– Nandakumar V, Commented Jun 23, 2015 at 9:15
Note that if you didn’t have to array_agg on player_id, you could discard the GROUP BY clause and just use first_value(team_name) OVER (PARTITION BY team_id ORDER BY team_name) in the SELECT. — Skippy le Grand Gourou
– Skippy le Grand Gourou, Commented Feb 8, 2024 at 9:54

Nandakumar V · Accepted Answer · 2015-07-17 06:36:23Z

71

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

answered Jul 17, 2015 at 6:36

Nandakumar V

4,7255 gold badges33 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Henry D Over a year ago

Wow. Thank you. This should really be the accepted answer. I can't create functions or add libraries, and I just need the first value and dont need to worry about duplicates.

Tung Le Over a year ago

Thanks. If I want to get the last element in the aggregation array, is it applicable to use array_agg(team_name)[-1] or what method should I use? I have tried with [-1] but it didn't work though.

shashikant kuswaha Over a year ago

how to add multiple columns in order by in array_agg

Quazi Irfan Over a year ago

Why do we need to add the parenthesis in the first place? array_agg returns an array of the argument type.

Patrick · Accepted Answer · 2015-06-23 08:17:02Z

5

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;

answered Jun 23, 2015 at 8:17

Patrick

33k7 gold badges73 silver badges102 bronze badges

4 Comments

Nandakumar V Over a year ago

I haven't thought of it actually, nice suggestion... but the fact is there are more tables to be joined and more select columns

Nandakumar V Over a year ago

Also I an expecting a generic function/implementation that can be used in other queries too..

Patrick Over a year ago

The above answers exactly your question, you can check in your own SQLFiddle. Of course, I cannot answer things you didn't ask about. So please edit your question or post a new question. What do you mean by "generic function/implementation", for instance?

Nandakumar V Over a year ago

sorry about the confusion... I have updated the question

Community · Accepted Answer · 2023-04-06 10:49:45Z

1

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

edited Apr 6, 2023 at 10:49

CommunityBot

11 silver badge

answered Nov 4, 2020 at 4:45

Pravin Solankar

112 bronze badges

Collectives™ on Stack Overflow

Return first element in array_agg()

3 Answers 3

4 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related