Query latest table in the BigQuery dataset

Question

I have a dataset containing tables with similar table names ending in yyyymmdd. For example:

myproject:mydataset.Sales20140815
myproject:mydataset.Sales20140816
myproject:mydataset.Sales20140817
myproject:mydataset.Sales20140818
...
myproject:mydataset.Sales20140903
myproject:mydataset.Sales20140904 

Is there any way to write the BigQuery to query the latest table in the dataset (for the above example it is myproject:mydataset.Sales20140904 )?

Answer 1

N.N. answer is good, but relying on the modification date is problematic if an old set of data is reimported that would erroneously be pulled as the "latest" Since the table_id explicitly lists the dates in the correct order it is best to use that value directly.

SELECT 
  *
FROM 
TABLE_QUERY(MyDATASET, 
      'table_id CONTAINS "MyTable" 
      AND table_id= (Select MAX(table_id) 
                              FROM MyDATASET.__TABLES__
                              where table_id contains "MyTable")'
            )