CODEWITHSUNDEEP
google-bigquery
Haipeng Su
Haipeng Su

Reputation: 2541

How to query the latest table using Standard Query

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.

Upvotes: 2

Views: 4557

Answers (3)

Haipeng Su
Haipeng Su

Reputation: 2541

To look it better, My query in Standard SQL Will be,

With LatestTable AS 
( 
  Select Max(_table_Suffix) as LatestTableName 
  From `project_id.dataset.*` 
  Where regexp_contains(_Table_Suffix, "tablename_2016") 
)

SELECT * 
FROM `project_id.dataset.*` 
WHERE _Table_Suffix = (Select LatestTableName from LatestTable)

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

SELECT * FROM `project_id.dataset.*` 
WHERE _TABLE_SUFFIX = (
  SELECT table_id 
  FROM `project_id.dataset.__TABLES_SUMMARY__` 
  ORDER BY table_id DESC 
  LIMIT 1)

Upvotes: 4

Elliott Brossard
Elliott Brossard

Reputation: 33705

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;

Upvotes: 3

Related Questions