title: Using ARRAY_AGG() with DISTINCT and ORDER BY with ORDINAL
body: <p>I have some data that I am trying to aggregate (greatly simplified here). The raw data uses a schema similar to the following:</p>
  
  <pre><code>UserID - STRING
  A - RECORD REPEATED
  A.Action - STRING
  A.Visit - INTEGER
  A.Order - INTEGER
  MISC - RECORD REPEATED
  ( other columns omitted here )
  </code></pre>
  
  <p>There are many actual records due to the "MISC" column, but I'm only trying to focus on the first 5 columns shown above. A sample of the raw data is shown below (note that the values shown are a sample only, many other values exist so these cannot be hard coded into the query) :</p>
  
  <p><strong>Table 0: (Raw data sample)</strong></p>
  
  <p>(empty values under UserID are as shown in BiqQuery - "A" fields are part of a nested record)</p>
  
  <p><img src="https://i.imgur.com/dBQYZQy.png" alt="Table0"></p>
  
  <p>My query produces the data shown in <strong>Table 1</strong> below. I am trying to use ARRAY_AGG with ORDINAL to select only the first two "Action"s for each user and restructure as shown in TABLE 2. </p>
  
  <pre><code>SELECT
    UserId, ARRAY_AGG( STRUCT(A.Action, A.Visit, A.Order)
            ORDER BY A.Visit, A.Order, A.Action ) 
    FROM
      `table` 
    LEFT JOIN UNNEST(A) AS A
  GROUP BY
      UserId
  </code></pre>
  
  <p><strong>Table 1: (Sample output of above query )</strong></p>
  
  <p><img src="https://i.imgur.com/AAJFk6n.png" alt="Table1"></p>
  
  <p><strong>Table 2: (The format needed)</strong></p>
  
  <p><img src="https://i.imgur.com/C61rheT.png" alt="Table2"></p>
  
  <p>So I need to:</p>
  
  <ol>
  <li>Get distinct "Action" values for each user</li>
  <li>Preserve the order ( UserID, Visit, Order )</li>
  <li>Show only the 1st and 2nd actions in one row</li>
  </ol>
  
  <p>My attempted query strategy was to ORDER BY UserID, Visit, Order and get DISTINCT values of Action using something like:</p>
  
  <pre><code>UserId,
  ARRAY_AGG(DISTINCT Action ORDER BY UserID, Visit, Order) FirstAction,
  ARRAY_AGG(DISTINCT Action ORDER BY UserID, Visit, Order) SecondAction
  </code></pre>
  
  <p>However, that approach produces the following error:</p>
  
  <blockquote>
    <p>Error: An aggregate function that has both DISTINCT and ORDER BY arguments can only ORDER BY columns that are arguments to the function</p>
  </blockquote>
  
  <p>Any thoughts on how to correct this error (or an alternative approach?)</p>