1

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?

7

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
     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;
  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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