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: http://stackoverflow.com/questions/3994556/eliminate-duplicate-array-values-in-postgres — user_0, Jun 23 '15 at 08: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, Jun 23 '15 at 09:15

score 62 · Accepted Answer · answered Jul 17 '15 at 06:36

62

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 '15 at 06:36

Nandakumar V

4,317
4
27
47

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. – Henry D May 03 '18 at 00:21
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. – Tung Le Jan 29 '20 at 18:45
how to add multiple columns in order by in array_agg – shashikant kuswaha Jun 22 '21 at 06:30
Why do we need to add the parenthesis in the first place? array_agg returns an array of the argument type. – Quazi Irfan Aug 21 '23 at 08:05

score 5 · Answer 2 · answered Jun 23 '15 at 08:17

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 '15 at 08:17

Patrick

29,357
6
62
90

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 Jun 23 '15 at 08:41
Also I an expecting a generic function/implementation that can be used in other queries too.. – Nandakumar V Jun 23 '15 at 08:42
2

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? – Patrick Jun 23 '15 at 08:44
sorry about the confusion... I have updated the question – Nandakumar V Jun 23 '15 at 08:53

score 1 · Answer 3 · edited Apr 06 '23 at 10:49

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 06 '23 at 10:49

Community

1
1

answered Nov 04 '20 at 04:45

Pravin Solankar

11
2

Return first element in array_agg()

3 Answers3