How to query the last table using Standard Query

advertisements

In Legacy SQL, I found this syntax very useful to query the latest table from the set of tables with Date Suffix.

SELECT *
FROM
TABLE_QUERY([project_id:dataset],
  'table_id = (SELECT MAX(table_id)
               FROM [project_id:dataset.__TABLES__]
               WHERE table_id CONTAINS "tablename_2016")
  ')

so, If I have set of tables such as tablename_20161205, tablename_20161206, tablename_20161219, the query will find the latest table which is tablename_20161219 in this case and I don't need to look through all tables and specify the latest one.

How do I achieve this in Standard SQL? Thx so much.


You can use a wildcard table (potentially with a filter to restrict the initial search). For example,

WITH RecentTables AS (
  SELECT *, _TABLE_SUFFIX
  FROM `project_id.dataset.*`
  WHERE _TABLE_SUFFIX >= "tablename_2016"
),
LatestTable AS (
  SELECT *
  FROM RecentTables
  WHERE _TABLE_SUFFIX = (SELECT MAX(_TABLE_SUFFIX) FROM RecentTables)
)
SELECT *
FROM LatestTable;