I have a table as below:

SELECT [EffectiveDate]
      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?



common table expression with row_number() version:

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;

cross apply version:

 select distinct
   , x.EffectiveDate
   , x.Rate

  from Vision t
    cross apply (
      select top 1
          , 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
  order by 
    row_number() over (
      partition by ModUser
          order by EffectiveDate desc

inner join version:

    , v.EffectiveDate
    , v.Rate
  from Vision v
    inner join (
        , MaxEffectiveDate = max(i.EffectiveDate) 
        from Vision i 
        group by ModUser
       ) as x on x.ModUser          = v.ModUser
             and x.MaxEffectiveDate = v.EffectiveDate;
  • Thanks for the long and detailed answer. I am unable to mark this as answer as i have low points. – Rinu Jan 3 '17 at 6:04

Your Answer

