Flag first occurrence of ID, mark second and third as 0
I have a dataset that looks something like this:
| ID | Age |
|---|---|
| AB138 | 19 |
| FR296 | 27 |
| JP311 | 13 |
| AB138 | 32 |
And I would like to add a column that “flags” the first instance as 1, and any further instances of that specific ID as 0 (order does not matter, so “first” is irrelevant). Below is an example output, based on the above dataset
| ID | Age | DistinctCt |
|---|---|---|
| AB138 | 19 | 1 |
| FR296 | 27 | 1 |
| JP311 | 13 | 1 |
| AB138 | 32 | 0 |
Is there an easier way of accomplishing this? Old approach looked something like this:
select ID
,COUNT(ID) as Distinct_Tst
into #step2
from #step1
group by ID
select a.*, b.Distinct_Tst,(1/cast(Distinct_Tst as decimal)) as DistinctCt
from #step1 as a
left join #step2 as b on (a.id=b.id)
order by ID