RANKING FUNCTIONS IN SQL SERVER

There are only 4 ranking functions present in SQL Server.

1. RANK()
2. DENSE_RANK()
3. ROW_NUMBER()
4. NTILE()

These all functions takes two input parameters via OVER() clause. One is PARTITION BY and the other one is ORDER BY.

LAB SCRIPT:

RANK()

RANK_EXAMPLE_01
RANK_EXAMPLE_01
RANK_EXAMPLE_02
RANK_EXAMPLE_02
  • In eg: 01 and 02, “LastName” Smith is repeating so they both received the same RANK value but it skipped the next RANK value of 3 and directly jumped over to 4. So RANK() function intentionally leaves a gap between numbering sequence when there is a tied value present before.
  • In eg:02 , the RANK function have been partitioned by “Status” and ordered by “LastName”. You can see a three group of numbering sequence (1-10), (1-2) and (1) for three distinct value of “Status” (Active, Has Tenure, On Leave).

DENSE_RANK()

  •  DENSE_RANK() function is same like RANK() and has same set of parameters. It assigns a same rank for tied values unlike like RANK() but manages to keep the numbering sequence rather skipping it after a tie.
DENSE_RANK_EXAMPLE
DENSE_RANK_EXAMPLE

ROW_NUMBER()

  • Unlike RANK() and DENSE_RANK(), ROW_NUMBER() keeps the numbering sequence intact in ties and does not skip any values. The sequence of this function are all continuous and unique.
ROW_NUMBER_01
ROW_NUMBER_EXAMPLE_01
ROW_NUMBER_EXAMPLE_02
ROW_NUMBER_EXAMPLE_02
  • In above example, the sequence have been grouped by “City” and then ordered by “LastName”.

NTILE()

  • Like ROW_NUMBER() it ignores ties.
  • NTILE() distributes rows into a specified number of groups.
  • We have total 13 records in our test table. To make NTILE understand simple , lets delete a record and play around with an even number 12 records.
NTILE_EXAMPLE
NTILE_EXAMPLE

Total Records = 12
NTILE_01 = 12 / 01 = 12 (One group)
NTILE_02 = 12/02 = 06 (Two groups; each of 6 members)
NTILE_03 = 12/03 = 04 (Three groups; each of 4 members)
NTILE_04 = 12/04 = 03 (Three groups; each of 3 members)
NTILE_05 = 12/05 = 2.4 (1st group 3 members) (2nd group 3 members) and rest 3 groups; each of 2 members.

  • NTILE() function tries to group the records as equal as possible in each group, if groups does not divide evenly then the lowest groups will have fewer members.

 

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

One thought on “RANKING FUNCTIONS IN SQL SERVER

  • August 3, 2016 at 6:08 pm
    Permalink

    Is it possible to use PARTITION BY with NTILE?

    Reply

Leave a Reply

%d bloggers like this: