I have a table as below:
SELECT [EffectiveDate] ,[Rate] [ModUser] FROM [Vision]
there are many rows with the ModUsers name. I need to provide the ModUsers name and get the latest rates.
How do I do that?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
There are many ways to do this. Here are some of them:
with cte as ( select * , rn = row_number() over ( partition by ModUser order by EffectiveDate desc ) from Vision ) select ModUser, EffectiveDate, Rate from cte where rn = 1;
select distinct t.ModUser , x.EffectiveDate , x.Rate from Vision t cross apply ( select top 1 ModUser , EffectiveDate , Rate from Vision i where i.ModUser = t.ModUser order by i.EffectiveDate desc ) as x;
top with ties version:
select top 1 with ties * from Vision where order by row_number() over ( partition by ModUser order by EffectiveDate desc );
inner join version:
select v.ModUser , v.EffectiveDate , v.Rate from Vision v inner join ( select ModUser , MaxEffectiveDate = max(i.EffectiveDate) from Vision i group by ModUser ) as x on x.ModUser = v.ModUser and x.MaxEffectiveDate = v.EffectiveDate;