Here is the sample of the data:
ID Value NumPeriod
------------------------
1681642 596.8 2
1681642 596.8 3
1681663 445.4 2
1681663 445.4 3
1681688 461.9 3
1681707 282.2 3
1681724 407.1 3
1681743 467 2
1681743 467 3
1681767 502 3
I want to group by the [ID] and take only the distinct values of [Value] within each group and take the "first" distinct [Value] according to [NumPeriod]. So the result would look something this:
ID Value NumPeriod
-------------------------
1681642 596.8 2
1681663 445.4 2
1681688 461.9 3
1681707 282.2 3
1681724 407.1 3
1681743 467 2
1681767 502 3
So I though something like this would work, but no luck:
select
ID, distinct(Value), NumPeriod
from
MyTable
group by
ID, Value, NumPeriod
order by
ID, NumPeriod
Any help would be appreciated. Thanks!
You can use a ranking function and a CTE: