Flags with different granularity in SQL

Question

I'd like to say that I'm still learning how to use SQL, so the questions I'm asking may be easy for some of you.

So, let's say I have a column called SAMPLE and a column called BATCH. Additionally, BATCH contains some SAMPLE, and for some reason there is a tag linked to the SAMPLE column (SAMPLE_FLG).

BATLCH_FLG is used to show that at least one sample contained in the batch is marked as 1.

You can visualize this scenario in this example image or in the table below:

| sample      | batch | sample_flg | batch_flg |
|:-----------:|:-----:|:----------:|:---------:|
| A111 - 1000 | A11   |      0     |     0     |
| A111 - 200  | A11   |      0     |     0     |
| A111 - 500  | A11   |      0     |     0     |
| B234 - 700  | B234  |      0     |     0     |
| B234 - 50   | B234  |      1     |     1     |
| B234 - 75   | B234  |      0     |     0     |
| C567 - 100  | C567  |      1     |     1     |
| C567 - 700  | C567  |      1     |     0     |
| C567 - 500  | C567  |      0     |     0     |

How to get a 1 for each batch in the BATCH_FLG column via SQL query? What I mean is that I shouldn't use 1 multiple times even though there are multiple samples per batch that are labeled 1 (last three rows in the example image).

The position of 1 in the BATCH_FLG column is not important.

Hope you can help me. Thanks.

Answer 1

You can use ROW_NUMBER() to produce the results you want. For example:

select t.*,
  case when row_number() over(patition by batch order by sample) = 1
       then 1 else 0 end as batch_flg
from t