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?


1 Answer 1


There are many ways to do this. Here are some of them:

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;
  • 1
    Thanks for the long and detailed answer. I am unable to mark this as answer as i have low points.
    – Rinu
    Commented Jan 3, 2017 at 6:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.