title: Partition without flatten in BigQuery
body: <p>Consider the following schema which contains transaction_no, date when it was created(created_at) &amp; Items(RECORD):</p>
  
  <pre><code>[
     {
        "mode":"REQUIRED",
        "name":"Id",
        "type":"INTEGER"
     },
     {
        "fields":[
           {
              "mode":"REQUIRED",
              "name":"Name",
              "type":"STRING"
           },
           {
              "mode":"REQUIRED",
              "name":"Qty",
              "type":"INTEGER"
           }
        ],
        "mode":"REPEATED",
        "name":"Items",
        "type":"RECORD"
     },
     {
        "mode":"REQUIRED"
        "name":"Transaction_no",
        "type":"STRING"
     },
     {
        "mode":"REQUIRED",
        "name":"Created_at",
        "type":"TIMESTAMP"
     }
  ]
  </code></pre>
  
  <p>Since this table contains, same item again, I use window function to grab the last item based on the transaction_no &amp; ordered by created_at like this to dump into another table:</p>
  
  <pre><code>SELECT *
  FROM (
    SELECT * 
    ROW_NUMBER() OVER(PARTITION BY transaction_no 
                      ORDER BY created_at DESC) as last
    FROM my_dataset.my_table
  )
  WHERE last = 1
  </code></pre>
  
  <p>Unfortunately this has following problems.</p>
  
  <ol>
  <li>this flattens the <code>Items</code> record. I have flatten result checkbox unselected</li>
  <li>It seems it only picks the first field from each repeatable record. </li>
  </ol>
  
  <p>What I want to do here is Remove duplicates without changes the table structure. Possible?</p>