title: Get the attributes of the most recent row in BigQuery?
body: <p>I'm working in BigQuery. I have a table <code>t1</code> which has address, postcode, price and date fields. I want to group this by address and postcode, an find the price of the most recent row for each address. </p>
  
  <p>How can I do this in BigQuery? I know how to get the address, postcode and most recent date:</p>
  
  <pre><code>SELECT
    ADDRESS, POSTCODE, MAX(DATE)
  FROM
    [mytable]
  GROUP BY
    ADDRESS,
    POSTCODE
  </code></pre>
  
  <p>But I don't know how to get the price of these rows matching these fields. This is my best guess, which does produce results - will this be correct?</p>
  
  <pre><code>SELECT 
    t1.address, t1.postcode, t1.date, t2.price
  FROM [mytable] t2
  JOIN 
  (SELECT
    ADDRESS, POSTCODE, MAX(DATE) AS date
  FROM
    [mytable]
  GROUP BY
    ADDRESS,
    POSTCODE) t1
  ON t1.address=t2.address 
     AND t1.postcode=t2.postcode 
     AND t1.date=t2.date
  </code></pre>
  
  <p>This seems to me like it should work, but some of the <a href="https://stackoverflow.com/questions/25269410/google-big-query-sql-get-most-recent-column-value">similar questions</a> have solutions that are much more complex. </p>