Tsql : ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

ROW_NUMBER, RANK, DENSE_RANK and PARTITION BY

A script to demo all of the above, as a reminder of Windowing functions...

SELECT
  name
 ,type_desc
 ,COUNT(*) OVER(PARTITION BY NULL) AS CountAllRecords
 ,ROW_NUMBER() OVER (ORDER BY name) AS RowNumberByName
 ,RANK()  OVER (ORDER BY type_desc) AS RankbyType
 ,DENSE_RANK()  OVER (ORDER BY type_desc) AS DenseRankbyType
 ,RANK() OVER (ORDER BY LEFT(Name,1)) AS RankByFirstCharacterofName
 ,DENSE_RANK() OVER (ORDER BY LEFT(Name,1)) AS DenseRankByFirstCharacterofName
 ,ROW_NUMBER() OVER (PARTITION BY LEFT(Name,1) ORDER BY LEFT(Name,1)) AS RowNumberPartitionedbyLeft1
FROM sys.objects
ORDER BY name