Get The Most Recent Record In A Table By Date And User

Question

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?

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