title: Get the first row of a nested field in BigQuery
body: <p>I have been struggling with a question that seem simple, yet eludes me.
  I am dealing with the public BigQuery table on bitcoin and I would like to extract the first transaction of each block that was mined. In other word, to replace a nested field by its first row, as it appears in the table preview. There is no field that can identify it, only the order in which it was stored in the table.</p>
  
  <p>I ran the <a href="https://bigquery.cloud.google.com/results/ether-bigquery:US.bquijob_3e78a409_1643280286c" rel="nofollow noreferrer">following query</a>:</p>
  
  <pre><code>#StandardSQL
  SELECT timestamp,
      block_id,
      FIRST_VALUE(transactions) OVER (ORDER BY (SELECT 1))
  FROM `bigquery-public-data.bitcoin_blockchain.blocks`
  </code></pre>
  
  <p>But it process 492 GB when run and throws the following error:</p>
  
  <pre><code>Error: Resources exceeded during query execution: The query could not be executed in the allotted memory. Sort operator used for OVER(ORDER BY) used too much memory..
  </code></pre>
  
  <p>It seems so simple, I must be missing something. Do you have an idea about how to handle such task?</p>