title: how to get the first (or any single) value in GROUP BY without ARRAY_AGG?
body: <p>I'm migrating some SQL from PostgreSQL 9.2 to Vertica 7.0, and I could use some help replacing postgres's cool <a href="http://www.postgresql.org/docs/9.2/static/functions-aggregate.html" rel="nofollow noreferrer">array_agg</a> feature with something that <a href="https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/SQLReferenceManual.htm?TocPath=SQL%20Reference%20Manual%7C_____0" rel="nofollow noreferrer">Vertica</a> (and possibly other RDBMS) supports, such as partitions and window functions. I'm new to these features, and I'd really appreciate your ideas.</p>
  
  <p>The (working) query using array_agg ( <strong><kbd><a href="http://sqlfiddle.com/#!12/a92d5/3/0" rel="nofollow noreferrer">sql fiddle demo</a></kbd></strong> ):</p>
  
  <pre><code>SELECT B.id, (array_agg(A.X))[1]
  FROM B, AB, A
  WHERE B.id = AB.B_id AND A.id = AB.A_id AND A.X IS NOT NULL
  GROUP BY B.id;
  </code></pre>
  
  <p>If I try to naively select A.X by itself without the aggregation (i.e., to let the RDBMS pick - actually works with MySQL and SQLite), postgres complains. Running the same query but with "A.X" instead of "(array_agg(A.X))<a href="http://www.postgresql.org/docs/9.2/static/functions-aggregate.html" rel="nofollow noreferrer">1</a>":</p>
  
  <pre><code>ERROR:  column "a.x" must appear in the GROUP BY clause or be used in an aggregate function
  LINE 1: SELECT B.id, A.X
  </code></pre>
  
  <p>I was thinking of trying a window function, e.g., something like from <a href="https://stackoverflow.com/questions/18686906/postgres-windows-function-with-aggregate-group-by/18692869">this question</a>:</p>
  
  <pre><code>SELECT email, FIRST_VALUE(email) OVER (PARTITION BY email)
  FROM questions
  GROUP BY email;
  </code></pre>
  
  <p>but I get the same error:</p>
  
  <pre><code>SELECT B.id, FIRST_VALUE(A.X) OVER (PARTITION BY A.id)
  FROM B, AB, A
  WHERE B.id = AB.B_id AND A.id = AB.A_id AND A.X IS NOT NULL
  GROUP BY B.id;
  
  ERROR:  column "a.x" must appear in the GROUP BY clause or be used in an aggregate function
  LINE 1: SELECT B.id AS id, FIRST_VALUE(A.X) OVER (PARTITION BY A.id)...
  </code></pre>
  
  <p>Note that we don't care so much about getting the first value, we just need any (ideally deterministic) single value.</p>
  
  <p>Thank you in advance.</p>