Useful ranking function ~row_number (), rank (), Dense_rank () three brothers

Ranking function three brothers, a look at the name will know, are for the ranking and born! But each has its own characteristics! Here's an example to illustrate the problem! (The following chestnuts do not use the partition by keyword to sort the entire result set)

Rank each value a rank, the same value in the same position, such as the first 2, the next value will be third, and so on, as shown in the following RANDNR column

Dense_rank each value of a ranking, and rank inconsistent with the place is that it does not jump, will be the same as three-way line down

Row_number each row a sorted value, when encountering the same sorting criteria, in order to give the value, corresponding to the performance of the following ROWNR column

; withCTE1 (ID,COL1) as(    SELECT 1,'AA'    UNION  All    SELECT 1,'AA'    UNION  All    SELECT 2,'BB'    UNION  All    SELECT 3,'CC'    UNION  All    SELECT 3,'CC'    UNION  All    SELECT 4,'DD'    UNION  All    SELECT 5,'EE')SELECTRANK () Over(ORDER  byID) asRanknr, Dense_rank () Over(ORDER  byID) asDensenr, row_number () Over(ORDER  byID) asRownr,*     fromcte1ranknr densenr rownr ID Col1-------------------- -------------------- -------------------- ----------- ----1                    1                    1                    1AA1                    1                    2                    1AA3                    2                    3                    2BB4                    3                    4                    3CC4                    3                    5                    3CC6                    4                    6                    4DD7                    5                    7                    5Ee

And then actually talking about the ranking function, the over clause is also very important.

The content after the over clause is basically as follows

Over (

Partition by AAA,BBB--represents grouping by AAA,BBB, each grouping starts at 1, and if the Partition by keyword is omitted, the entire result set is sorted as a group

ORDER BY CCC asc,ddd desc--means to give sort values according to CCC,DDD's execution order, what if there are no specific sort orders? can be used (select 1) or NEWID () This is used for random sorting.

)

The three brothers are finished talking about other things

However, the over clause has a more useful usage when using the window aggregation function (not the sorting function). The over clause can be specified in addition to the grouping (a version that appears to be 2012 is supported, and a version prior to 2012 only supports all aggregations of the result set).

For example, I still take back the test table that generated 500 rows of data (the data is not completely pasted), and sometimes it is quite useful to do comparisons and statistics.

SELECTID,SUM(ID) Over(ORDER  by(SELECT 1) ROWSbetweenunbounded preceding and  CurrentROW) asNR1,--accumulate from the first line to the current row        SUM(ID) Over(ORDER  by(SELECT 1) ROWSbetween 1Preceding and  CurrentROW) asNR2,--sum of previous line and current line        SUM(ID) Over(ORDER  by(SELECT 1) ROWSbetween  CurrentROW and  1Following) asNr3--sum the following line and the current line     fromdbo. Tmp123id Nr1 Nr2 Nr3----------- ----------- ----------- -----------1           1           1           32           3           3           53           6           5           74           Ten          7           95            the          9            One6            +           One           -7            -           -           the8            $           the           -9            $           -           +Ten           -           +           + One           the           +           at A           +           at           - -           the           -           - -           the          -           in

Useful ranking function ~row_number (), rank (), Dense_rank () three brothers