ROW_NUMBER ( ) OVER ( [ PARTITION_BY_clause ] order_by_clause )

select Department
    ,LastName
    ,Rate
    ,row_number() over(order by Rate) as [row number]
    ,rank() over(order by rate) as rate_rank
    ,dense_rank() over(order by rate) as rate_dense_rank
    ,ntile(4) over(order by rate) as quartile_by_rate
from #data

;with cte_data as 
(
select 'Document Control' as Department,'Arifin' as LastName,17.78 as Rate 
union all 
select 'Document Control','Norred',16.82 
union all 
select 'Document Control','Kharatishvili',16.82
union all 
select 'Document Control','Chai',10.25 
union all 
select 'Document Control','Berge',10.25 
union all 
select 'Information Services','Trenary',50.48
union all 
select 'Information Services','Conroy',39.66 
union all 
select 'Information Services','Ajenstat',38.46
union all 
select 'Information Services','Wilson',38.46
union all 
select 'Information Services','Sharma',32.45
union all 
select 'Information Services','Connelly',32.45
union all 
select 'Information Services','Berg',27.40
union all 
select 'Information Services','Meyyappan',27.40
union all 
select 'Information Services','Bacon',27.40
union all 
select 'Information Services','Bueno ',27.40
)
select Department,LastName,Rate into #data from cte_data
go

LAG (scalar_expression [,offset] [,default])    OVER ( [ partition_by_clause ] order_by_clause )
LEAD ( scalar_expression [ ,offset ] , [ default ] )  OVER ( [ partition_by_clause ] order_by_clause )

select DepartMent ,LastName,Rate
    ,lag(Rate,1,0) over(partition by Department order by LastName) as LastRate
    ,lead(Rate,1,0) over(partition by Department order by LastName) as NextRate
from #data order by Department ,LastName

declare @t table
  (
      c1 int identity
      ,c2 date
  )
 
  insert into @t (c2)
  select '20120101'
  union all
  select '20120201'
  union all
  select '20120110'
  union all
  select '20120221'
  union all
  select '20120121'
  union all
  select '20120203'
 
  select c1,c2
      ,LAG(c2) OVER (ORDER BY c2) as previous_c2
      ,DateDiff(day,LAG(c2) OVER (ORDER BY c2),c2) as diff
  from @t
  order by c2

LAST_VALUE ( [scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )
FIRST_VALUE ( [scalar_expression ] ) OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

select Department, LastName, Rate, 
    row_number() over (partition by Department order by LastName) as FIRSTVALUE, 
    first_value(Rate) over (partition by Department order by LastName  rows between unbounded preceding and unbounded following) as FIRSTVALUE,
    last_value(Rate) over (partition by Department   order by LastName   rows between unbounded preceding and unbounded following) as LASTVALUE
from #data
order by Department, LastName;

select Department,LastName ,Rate
    ,cume_dist() over(partition by Department order by Rate) as CumeDist
    ,percent_rank() over(partition by Department order by Rate) as PtcRank
    ,rank() over(partition by Department order by Rate asc) as rank_number
    ,count(0) over(partition by Department) as count_in_group
from #data
order by DepartMent
    ,Rate desc

select Department  ,LastName  ,Rate
    ,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Rate) OVER (PARTITION BY Department) AS MedianCont
    ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Rate) OVER (PARTITION BY Department) AS MedianDisc
    ,row_number() over(partition by Department order by Rate) as rn
from #data order by DepartMent ,Rate asc