title: BigQuery argmax: Is array order maintained when doing CROSS JOIN UNNEST
body: <p><strong>Question:</strong></p>
  
  <p>In BigQuery, standard SQL, if I run</p>
  
  <pre><code>SELECT *
  FROM mytable
  CROSS JOIN UNNEST(mytable.array)
  </code></pre>
  
  <p>Can I be certain that the resulting row order is the same as the array order?</p>
  
  <p><strong>Example:</strong></p>
  
  <p>Let's say I have the following table <code>mytable</code>:</p>
  
  <pre><code>Row | id   | prediction
  1   | abcd | [0.2, 0.5, 0.3]
  </code></pre>
  
  <p>If I run <code>SELECT * FROM mytable CROSS JOIN UNNEST(mytable.prediction)</code>, can I be certain that the row order is the same as the array order? I.e. will the resulting table always be:</p>
  
  <pre><code>Row | id   | unnested_prediction
  1   | abcd | 0.2
  2   | abcd | 0.5
  3   | abcd | 0.3
  </code></pre>
  
  <p><strong>More background on use case (argmax):</strong></p>
  
  <p>I'm trying to find the array index with the largest value for the array in each row (argmax), i.e. the second element (<code>0.5</code>) in the array above. My target output is thus something like this:</p>
  
  <pre><code>Row | id   | argmax
  1   | abcd | 2
  </code></pre>
  
  <p>Using <code>CROSS JOIN</code>, a <code>DENSE_RANK</code> window function ordered by the <code>prediction</code> value and a <code>ROW_NUMBER</code> window function to find the argmax, I am able to make this work with some test data. You can verify with this query:</p>
  
  <pre><code>WITH predictions AS (
    SELECT 'abcd' AS id, [0.2, 0.5, 0.3] AS prediction
    UNION ALL
    SELECT 'efgh' AS id, [0.7, 0.2, 0.1] AS prediction
  ),
  ranked_predictions AS (
    SELECT 
      id,
      ROW_NUMBER() OVER (PARTITION BY id) AS rownum, -- This is the ordering I'm curious about
      DENSE_RANK() OVER (PARTITION BY id ORDER BY flattened_prediction DESC) AS array_rank
    FROM
       predictions P
    CROSS JOIN
      UNNEST(P.prediction) AS flattened_prediction
  )
  SELECT
    id,
    rownum AS argmax
  FROM
    ranked_predictions
  WHERE array_rank = 1
  </code></pre>
  
  <p>It could just be a coincidence that <code>ROW_NUMBER</code> behaves well in my tests (i.e. that it is ordered according to the unnested array), so it would be nice to be certain.</p>