BigQuery: Use COUNT as LIMIT

1
votes

I want to select everything from mytable1 and combine that with just as many rows from mytable2. In my case mytable1 always has fewer rows than mytable2 and I want the final table to be a 50-50 mix of data from each table. While I feel like the following code expresses what I want logically, it doesn't work syntax wise:

Syntax error: Expected "@" or integer literal or keyword CAST but got "(" at [3:1]

(SELECT * FROM `mytable1`)
UNION ALL (
    SELECT * FROM `mytable2`
    LIMIT (SELECT COUNT(*) FROM`mytable1`)
)

Using standard SQL in bigquery

2
sqlgoogle-bigquery
Does this work? SELECT * FROM mytable2 LIMIT (SELECT COUNT(*) FROM mytable1) ... some products don't allow subqueries in some locations, this may be one of those – Paul Maxwell
you should give us more details about your use case - so we might find something to get trick done. otherwise the only option I see - just use client of your choice to first get rows count in first table and then use it in second query as a parameter or just simply constructing your query as a text, etc. – Mikhail Berlyant

2 Answers

3
votes

The docs state that LIMIT clause accept only literal or parameter values. I think you can ROW_NUMBER() the rows from second table and limit based on that:

SELECT col1, col2, col3
FROM mytable1

UNION ALL

SELECT col1, col2, col3 
FROM (
    SELECT col1, col2, col3, ROW_NUMBER() OVER () AS rn
    FROM mytable2
) AS x
WHERE x.rn <= (SELECT COUNT(*) FROM mytable1)
0
votes
  • Each SELECT statement within UNION must have the same number of columns
  • The columns must also have similar data types
  • The columns in each SELECT statement must also be in the same order

As your mytable1 always less column than mytable2 so you have to put same number of column by selection

select col1,col2,col3,'' as col4 from mytable1 --in case less column you can use alias 
union all
select col1,col2,col3,col4 from mytable2