title: Querying Big Query tables with multiple repeated records
body: <p>I have a table with three repeated record types.  </p>
  
  <p>A sample of the table looks like:</p>
  
  <pre><code>STR string,
  SKU integer,
  DAILY_SALES record repeated,
  DAILY_SALES.SLS_DT DATE,
  DAILY_SALES.SLS_AMT FLOAT,
  PROD_HIER record repeated,
  PROD_HIER.PROD_DESC STRING,
  PROD_HIER.DEPT   integer,
  PROD_HIER.EFF_BGN_DT DATE,
  STR_HIER record repeated,
  STR_HIER.STR_NM string,
  STR_HIER.DIV    string,
  STR_HIER.EFF_BGN_DT DATE
  </code></pre>
  
  <p>For every STR/SKU record, I need to get data from PROD_HIER which has the maximum (latest) EFF_BGN_DT and also get the record from STR_HIER which has lastest STR_HIER.EFF_BGN_DT.  </p>
  
  <p>It would help if this can be done both in legacy sql (for external tools) and standard SQL.  Any thoughts are greatly appreciated.</p>