Reputation: 389
I couldn't find an answer to this specific to BigQuery. Basically I want to be able to count ROW_NUMBER in a query and then modify the row number with basic math operators (add, subtract, etc) without using a subquery.
I know the following code works, but to me it seems unnecessary to use a subquery just to append basic math operators
SELECT
id,
title,
language,
rn+5
FROM
(
SELECT
id,
title,
language,
ROW_NUMBER() OVER() AS rn
FROM [publicdata:samples.wikipedia] LIMIT 1000
)
However when I try to add the plus sign without the subquery I get an error
SELECT
id,
title,
language,
ROW_NUMBER() OVER() +5 AS rn_offset,
FROM [publicdata:samples.wikipedia] LIMIT 1000
Is what I'm trying to do possible in BigQuery without a subquery, or is my syntax in error?
Thanks, Onji
Upvotes: 1
Views: 1201
Reputation: 3251
This is a limitation of BigQuery's legacy SQL - various computations cannot be composed without using a subselect. It is unfortunate, but unlikely to be changed at this point. This is just one example among many.
However, you can do this in standard SQL without a subquery:
SELECT
id,
title,
language,
ROW_NUMBER() OVER() +5 AS rn_offset
FROM `publicdata.samples.wikipedia` LIMIT 1000
If limitations like this annoy you while using legacy SQL, I suggest looking into using standard SQL.
Upvotes: 1