Sql Server - Counting partitions with Ranking functions

Question

Consider the following sample data:

Name    Email  RowRank   PartitionRank
----    -----  -------   -------------
a1      e1      1         1
a1      e1      2         1
a2      e2      1         2
a2      e2      2         2
a2      e2      3         2

I am trying to write a query which partitions the data by Name + Email, then gives me index of a row in each partition and the number of partition itself (the last 2 columns list the expected output).

I have been trying with various queries, producing RowRank is easy with ROW_NUMBER, however I am unable to produce partition rank using any of the 4 ranking functions (NTILE, RANK, DENSE_RANK or ROW_RANK). I am sure this should be possible, but can't get onto it myself.

Here's a sample query:

SELECT Name, Email,
        ROW_NUMBER() OVER (PARTITION BY Name, Email ORDER BY Email, DisplayName) AS RowRank,
        NTILE(1) OVER (PARTITION BY Name, Email ORDER BY Email, DisplayName) AS PartitionRank
FROM Zone.MyTable

Can someone please help me get the partition rank correct.

Answer 1

You need a DENSE_RANK() over the whole output (no partitioning)

SELECT Name, Email,
        ROW_NUMBER() OVER (PARTITION BY Name, Email ORDER BY Email, DisplayName) AS RowRank,
        DENSE_RANK() OVER (ORDER BY Name, Email) AS PartitionRank
FROM Zone.MyTable

Answer 2

This works for me:

select *
  , RowRank = row_number() over (partition by Name order by Email)
  , PartitionRank = dense_rank() over (order by Name)
from MyTable

SQL Fiddle with demo.

Answer 3

select * ,row_number() over (order by Salary desc) as [Row_number],rank() over (order by Salary desc) as [Rank],dense_rank() over (order by Salary desc) as [Dense_rank],ntile(3) over (order by Salary desc) as [Ntile] from Employee 

Check example in the URL given below

http://www.freshcodehub.com/Article/50/implement-ranking-functions-in-sql-server