Home > Software engineering >  Only return rows where Status Online is the only status is applicable (perhaps a coalesce function o
Only return rows where Status Online is the only status is applicable (perhaps a coalesce function o

Time:08-04

ID Status Date
1 Online 2022-06-31
1 Offline 2022-07-28
2 Online 2022-08-01
3 Online 2022-07-03
3 Stationary 2022-07-05
4 Offline 2022-05-02
5 Online 2022-04-04
5 Online 2022-04-06

The output I'm looking for in SQL/BigQuery

ID Status Date
2 Online 2022-08-01
5 Online 2022-04-04
5 Online 2022-04-06

So what SQL query I would want to return is the rows for ID 2 & 5 as they both only have Online for that ID. 1 & 3 should not be included even though they have one "online" status. I am only looking for IDs which have Online as their only status.

I just need a query for the ID where the only status for that ID is Online.

I'm assuming I need to use the qualify function but not sure how to go about this, thanks for the help in advance

CodePudding user response:

Consider also below option

select * from your_table 
qualify countif(status != 'Online') over (partition by id)= 0    

if applied to sample data in your question - output is

enter image description here

  • Related