llerdal llerdal - 3 months ago 8
SQL Question

Sql return group of records till bit is true for all records

I have a group of records that update over time and I want them to be return until all of the records have one of the columns, a bit column true.

schema

| ID | BatchID | Value | SampleCompleted |
| 1 | 4444 | 3 | 1 |
| 2 | 4444 | 5 | 1 |
| 3 | 4444 | 20 | 1 |
| 4 | 4444 | 50 | 1 |
| 5 | 4444 | NULL | NULL |


So in the example above I want to return all of those record returned until record 5 has been update with the SampleCompleted bit to 1. All of the records that are grouped will have the same batch ID. I know this is probably simple but I'm having a hard time figuring out the logic.

Answer

Here's one way to do it:

SELECT * FROM MyTable
WHERE BatchID IN
(
    SELECT BatchID FROM MyTable
    WHERE SampleCompleted IS NULL
)