Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My Experiments with SQLServer
Browse by Tags · View All
SQLServer 126
SQLServer 2008 R2 91
SQLServer 2008 86
SQLServer 2005 82
SQL 60
Database 59
Sql And Me 57
Tips & Tricks 28
SQL Server 27
SQL FAQ 26

Archive · View All
May 2011 25
June 2011 21
July 2011 21
August 2011 19
April 2011 16
January 2013 4
May 2012 3
April 2012 3
October 2011 3
February 2013 2

Vishal Gajjar's Blog

SQL Server – "Denali" – PERCENT_RANK() Analytic Function

Aug 19 2011 2:52AM by Vishal Gajjar   

PERCENT_RANK() returns the position of a row within the result set. In contrast to RANK() function, PERCENT_RANK() ranks rows between 0 and 1, both inclusive.

Computation formula used by PERCENT_RANK():

(RANK() – 1) / (Number of Rows – 1)

where, RANK() is the rank of the row within the result set.

Using PERCENT_RANK():

PERCENT_RANK() takes and ORDER BY clause as argument:

SELECT [Year], [Month], [Amount],
       PERCENT_RANK() OVER (ORDER BY [Amount]) [PERCENT_RANK],
       RANK() OVER (ORDER BY [Amount]) [RANK]
FROM   dbo.SalesData

Result Set:

Year        Month       Amount                PERCENT_RANK           RANK
2011        1           1000.00               0                      1
2011        2           2000.00               0.142857142857143      2
2010        4           2000.00               0.142857142857143      2
2011        3           3000.00               0.428571428571429      4
2011        4           4000.00               0.571428571428571      5
2010        1           5000.00               0.714285714285714      6
2010        2           6000.00               0.857142857142857      7
2010        3           7000.00               1                      8

(8 row(s) affected)

PERCENT_RANK for row 4 (Year = 2011, Month = 3) is calculated as:

(4 – 1.00) / (8 – 1.00) = 0.4285714..

PARTITION BY:

You can use PARTITION BY clause with PERCENT_RANK() to divide result set into partitions:

SELECT [Year], [Month], [Amount],
       PERCENT_RANK() OVER (PARTITION BY [Year] ORDER BY [Amount]) [PERCENT_RANK],
       RANK() OVER (PARTITION BY [Year] ORDER BY [Amount]) [RANK]
FROM   dbo.SalesData

Result Set:

Year        Month       Amount                PERCENT_RANK           RANK
2010        4           2000.00               0                      1
2010        1           5000.00               0.333333333333333      2
2010        2           6000.00               0.666666666666667      3
2010        3           7000.00               1                      4
2011        1           1000.00               0                      1
2011        2           2000.00               0.333333333333333      2
2011        3           3000.00               0.666666666666667      3
2011        4           4000.00               1                      4

(8 row(s) affected)

Hope This Helps! Cheers!


Republished from Sql&Me [31 clicks].  Read the original version here [32134 clicks].

Vishal Gajjar
46 · 4% · 1276
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]