sql - Google Bigquery: Remove rows where column value is distinct (count = 1) without referencing table -

given table, wish remove rows column value distinct.

so if want column2 on matrix

     c1 c2 c3 = |1  2  4 |     |1  2  5 |     |1  1  6 |

yields

     c1 c2 c3 = |1  2  4 |     |1  2  5 |

this can done by

select * table c2 in (select c2 table group c2 having count(*) > 1)

unfortunately, in middle of subquery, don't have data stored in table , don't want create view need filtering in 1 query.

any ideas on how can still filter out distinct rows respect single column without referencing table in subquery?

the solution should of form:

select <something goes here> <the subquery outputs goes here> <anything want here legal bigquery - e.g. can't reference a>

and there no table reference.

bigquery supports window functions, might able this:

select t.* (select t.*, count(*) on (partition col2) cnt       table t      ) t cnt >= 2;

this still references table in subquery, reference table.

Remember