Group 2 columns and exclude groups containing any nulls-CodePudding

I want to only return the distinct collection_time group, for only collection_time groups that contain at least 1 non null row for the blocking_session_id field. I can't get it right. Here is some example data. In the example below, the 2 rows would be returned. The last grouping of collection_time has all nulls for blocking_session_id so I would not want that group in my result set. Thanks for any tips.

2022-12-02 13:13:22.167 
2022-12-02 13:13:43.873 

collection_time               blocking_session_id
2022-12-02 13:13:22.167       525
2022-12-02 13:13:22.167       481
2022-12-02 13:13:22.167       NULL
2022-12-02 13:13:22.167       NULL
2022-12-02 13:13:43.873       NULL
2022-12-02 13:13:43.873       NULL
2022-12-02 13:13:43.873       591
2022-12-01 20:25:12.847       NULL
2022-12-01 20:25:12.847       NULL
2022-12-01 20:25:12.847       NULL
2022-12-01 20:25:12.847       NULL
2022-12-01 20:25:12.847       NULL

CodePudding user response：

You can simply select all distinct collection_times where there are no NULL blocking_session_ids

SELECT DISTINCT collection_time
FROM your_table
WHERE blocking_session_id IS NOT NULL