How do I get the ID column (& other columns) that do not have the same value in the city column?

user_17679670 0
Viewed 41

Let's say I have this: https://i.imgur.com/skAJi07.png

I would like the result from a query to look like this: https://i.imgur.com/3zJVLtX.png

Could someone help me out? Essentially all I am trying to do is not get ids where all city values for the same id is NOT Miami.

I have tried a couple of group by/having(sum) and also tried where not exists. But I can't get an answer what I want.

In BigQuery doing where not exists, I get this error:

Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into efficient JOIN.

Thanks in advance!

google-bigquery sql
1 Answers

Consider below approach

select *
from your_table
where true 
qualify count(*) over win != countif(city =  'Miami') over win
window win as (partition by id)

if applied to sample data in your question - output is

enter image description here

Here count(*) over win != countif(city = 'Miami') over win is literally translated as where all city values for the same id is NOT Miami

user_52219440
Related