remove results from query if group is null

I have the below table:

Criteria:

Do not show results for grpid if there is a null custuserid for all grpid

grpid 145 should be seen as there is a custuserid on the second row.

Custid    grpid   custuserid   date
  101       145                12/30/19
  101       145     dellf      1/1/20
  101       255     dellf      1/1/20
  201       456                1/1/20
  201       555     smithr     1/1/20

output:

Custid    grpid   custuserid   date
  101       145                12/30/19
  101       145     dellf      1/1/20
  101       255     dellf      1/1/20
  201       555     smithr     1/1/20

Best way to filter out these results?

I was thinking first_value could be used but is there a better way?

Answer

I would simpy use exists:

select t.*
from mytable t
where exists (
    select 1 from mytable t1 where t1.grpid = t.grpid and t1.custuserid is not null
)

The correlated subquery ensures that at least one record with the same grpid has a non-null custuserid. For performance, you want an index on (grpid, custuserid).

You could also use window functions:

select *
from (
    select t.*, max(custuserid) over(partition by grpid) max_custuserid 
    from mytable t
) t
where max_custuserid is not null

Or, you can join an aggregate query:

select t.*
from mytable t
inner join (
    select 
        grpid, 
        max(custuserid) 
    from mytable 
    group by grpid 
    having max(custuserid) is not null
) x on x.grpid = t.grpid

Which option performs best would depend on your dataset (size, cardinality, …).