SQL 2012 has introduced several windows functions and below SQL code gives an idea on how to they work. Some of the functions such as row_number(),rank(),dense_rank(),Ntile() and grouping _sets() work in older versions as well.
Create Table WindowFunctions(id int,Sname varchar(20),Scity varchar(20))
GO
Insert into WindowFunctions
Values(1,'Martin','Dallas'),
(2,'Martin','Dallas'),
(3,'Martin','Houston'),
(4,'Martin','Austin'),
(5,'Sheri','Dallas'),
(6,'Sheri','Dallas'),
(7,'Sheri','Houston'),
(8,'Sheri','Austin')
--Row Number():
Select *,Row_Number() Over (Partition by Sname Order by Scity) as [RowNumber] from WindowFunctions
--Rank():
Select *,Rank() Over (Partition by Sname Order by SCity) as [Rank] from WindowFunctions
--Dense Rank():
Select *,Dense_Rank() Over (Partition by Sname Order by Scity) as [DenseRank] from WindowFunctions
--Lag():
Select *,LAG(id) over (Partition by Sname Order by Scity) as [LAG_ID] from WindowFunctions
--Lead():
Select *,LEAD(id) over (Partition by Sname Order by Scity) as [LEAD_ID] from WindowFunctions
--First Value():
Select *,FIRST_VALUE(id) Over (Partition by Sname Order by Scity ROWS between 1 preceding and 2 following) as FirstValue from WindowFunctions
--Last Value():
Select *,LAST_VALUE(id) Over (Partition by Sname Order by Scity ROWS between current row and 2 following) as LastValue from WindowFunctions
--Sum():
Select *,SUM(id) OVER (Partition by Sname Order by Scity ROWS between current row and 2 following) as [Sum] from WindowFunctions
--Grouping Sets():
Select count(id) as [Count_of_ID],Sname,Scity from WindowFunctions GROUP BY Grouping Sets ((sname,scity),(Sname),(Scity))
--Max():
Select *,MAX(id) OVER (Partition by Sname Order by Scity ROWS between unbounded preceding and unbounded following) as [MaxValue] from WindowFunctions
--Min():
Select *,Min(id) OVER (Partition by Sname Order by Scity ROWS between unbounded preceding and unbounded following) as [MinValue] from WindowFunctions
--NTILE():distributes the result set over the NTILE range
Select *,NTILE(3) OVER (Order by Scity) as [DistrutionRange] from WindowFunctions