Quick Reference For Ranking and Sums with Ranking

/*

Developer: Christopher Harsch

A quick and simple example of ranking and summing functions against a rank function.

Ranking Functions in SQL (some syntax only applies to 2012 sql server)

https://msdn.microsoft.com/en-us/library/ms189798.aspx

-- This shows the use of over clauses in sql which are used below.

https://msdn.microsoft.com/en-us/library/ms189461.aspx

*/

IF Object_id('tempdb..#tmp_rank') IS NOT NULL

  BEGIN

      DROP TABLE #tmp_rank

  END

CREATE TABLE #tmp_rank

  (

     myid   INT,

     value1 VARCHAR(255),

     value2 VARCHAR(255),

        DecimalValue decimal(18,2)

  )

INSERT INTO #tmp_rank

            (myid,

             value1,

             value2,

                      DecimalValue)

SELECT 1,

       'A',

       'B',

          10

UNION ALL

SELECT 1,

       'A',

       'C' ,

          10

UNION ALL

SELECT 1,

       'A',

       'D' ,

          10

UNION ALL

SELECT 1,

       'A',

       'B' ,

          10

UNION ALL

SELECT 2,

       'B',

       'B' ,

          10

UNION ALL

SELECT 2,

       'B',

       'B' ,

          10

UNION ALL

SELECT 1,

       'C',

       'D' ,

          10

UNION ALL

SELECT 1,

       'D',

       'B' ,

          10

UNION ALL

SELECT 1,

       'C',

       'A' ,

          10

IF object_id('tempdb..#tmp_ranks') IS NOT NULL

BEGIN

   DROP TABLE #tmp_ranks

END

SELECT *,

       Rank()

         OVER (

           partition BY tr.value1

           ORDER BY value2) AS myRank        -- Ranks with numbering being numerically continuous IE 1,1,3,4

       ,

       Dense_rank()

         OVER (

           partition BY tr.value1

           ORDER BY value2) AS mydenserank        -- This provides contiguous rank numbering within ranked sets, 1,1,2,3

       ,

       Row_number()

         OVER (

           partition BY tr.value1

           ORDER BY value2) AS myrownum -- Individually numbers

       ,

       Dense_rank()

         OVER (

           partition BY 1

           ORDER BY value1) AS myDenseRankgroupnumber        -- by partitioning by a static number you can use it as a group number.

       ,

       Rank()

         OVER (

           partition BY 1

           ORDER BY value1) AS myRankGroupNumber        -- Ranks with numbering being numerically continuous IE 1,1,3,4 not the best for group numbers

       , sum(DecimalValue) over(partition by myid) as SumForAllInId  -- Lets you sum by a grouping

       , sum(DecimalValue) over(partition by myid,value1) as SumForAllValue1InID -- Lets you sum within groups and sub groups

into #tmp_ranks

-- Individually numbers within a partitioned set.

FROM   #tmp_rank tr

-- This shows how you can use a two step ranking, then summing process.

-- In later versions of SQL this can be moved upward into the above query.

select

*

, SUM(1) over (Partition By myDenseRankgroupnumber) as  TotalInDenseGroup -- Count total records in a group that was created by the dense rank

, SUM(DecimalValue) over (Partition By myDenseRankgroupnumber) as  SumTotalInDenseGroup -- Sum records in a group that was created by the dense rank

from #tmp_ranks