Search code examples
sqlgoogle-bigquerytranslationteradatarow-number

BigQuery select row_number() over (order by tablename) from dbc.tables

I am translating a very large CTE Teradata query and got stuck at this following portion that is its own subquery, which is being cross joined into a much large subquery.

How can I translate this query into Bigquery?

(select row_number() over (order by tablename) subsequent_month from dbc.tables qualify row_number() over (order by tablename) <= 24)

Thoughts guys?

Solution

  • Below is for BigQuery Standard SQL 

    #standardSQL
SELECT subsequent_month FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY tablename) subsequent_month 
  FROM dbc.tables 
) WHERE subsequent_month <= 24