IURIX
Begin main content

difference between ntile and denserank [www.c-sharpcorner.com]

  • DENSE_RANK() is similar to RANK() but the only difference is DENSE_RANK() does not skip any rank, i.e. leaving no gap(s) between the gap(s).
  • NTILE() is used to distribute the rows in to the rows set with a specific number of groups.

 

 

DENSE_RANK()

 
It is similar to RANK() but the only difference is DENSE_RANK() does not skip any rank, i.e. leaving no gap(s) between the gap(s).
 
Generally, DENSE_RANK() is used to provide ranking to the records as mentioned in Example-6.
 
Syntax
 
DENSE_RANK () OVER ([PARTITION BY value_exp, ... [ n ]] ORDER BY_clause)
 
Example 6
 
How to display member names in rank given by their points. For example highest point will come first and lowest last? 
  1. SELECT DENSE_RANK() OVER( ORDER BY Point descAS [Rank], Name, Point FROM Member  
Output
 
 
As you can see here, the default order is ascending order.
 
Example 7
 
How to display third highest point? 
  1. select Point from (  
  2. SELECT DENSE_RANK() OVER( ORDER BY Point descAS [Rank], Name, Point FROM Member  
  3. as tmp where tmp.[Rank] = 3  
Output
 
 
Note that, generally, this question is asked in interview question such as how to get second highest salary.
 
Partition By clause
 
You can also use partition by clause with it to divide result with some specified partition.
 

NTILE()

 
It is used to distribute the rows in to the rows set with a specific number of groups.
 
It provides each row in the result set with a unique group number. It starts from 1 that shows the group that belongs to this group. N is a positive number and it distributes the rows set into.
 
Generally, NTILE () is used to divide the records into the specific number of in Example-8.
 
Syntax
 
NTILE(buckets) OVER ([PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ...)
 
Example 8
 
How to divide the members in 3 groups by their points? For example so that the highest point will come first and the lowest last into the group? 
  1. SELECT NTILE(3) OVER( ORDER BY Point descAS [Rank], Name, Point FROM Member  
Output
 
 

 

 

 


12:29 PM, 15 Sep 2021 by Iuri Sampaio Permalink

Add comment