Using SQL I have a list of credits for a course by year. I want to get a count of how many values the 'credits' attribute has been over years. If a value occurs twice (non consecutively) I want it counted. As follows, you can see there are 3 distinct values for CREDITS (5, 7.5, 6.5) over the timeframe but if credits have been values (5, 7.5, 6.5 and then back to 5) over the years, I want 4 as the result.
Dense rank is not working and does not give me what I want as just gives me distinct values in the timeframe, rather than 4 (non distinct) values across the timeframe given, with respect to time. Is this doable in SQL?
You can use a running sum with a case expression that checks if the credits value has been changed:
select year_, credits,
sum(case when credits<>pre_credits then 1 else 0 end)
over (order by year_ desc) + 1 res_rnk
from
(
select *,
lag(credits, 1, credits) over (order by year_ desc) pre_credits
from tbl
) t
order by year_ desc