title: Return first element in array_agg()
body: <p>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 <code>array_agg()</code>. I have written the query as follows:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This query gives me the result as below, in the result set the <code>teamname</code> is being repeated (exactly to the no. of players)</p>
  
  <pre><code>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"}
  </code></pre>
  
  <p>Is there any way to return the result like this     </p>
  
  <pre><code>team_id             team_name                                                                   playerdetails
  1                   Australia                       {"5##Glenn Donald McGrath","6##Shane Warne","2##Steve Waugh","1##Adam Gilchrist"}
  </code></pre>
  
  <p>I have achieved it using a subquery, but want to know if its possible to write it without the subquery   </p>
  
  <pre><code>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  
  </code></pre>
  
  <p>The <a href="http://sqlfiddle.com/#!15/a885d" rel="noreferrer">sqfiddle</a> is here. And I am using Postgresql 8.4   </p>
  
  <p><strong>[EDIT]</strong><br>
  I was actually thinking of hack to the <code>GROUP BY</code> limitation <code>column "team.team_status" must appear in the GROUP BY clause or be used in an aggregate function</code> when try to retrieve the a column which was not been specified in group by   </p>
  
  <pre><code>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   
  </code></pre>