-- =============================================
--Author:tomtom
--Create date:2015.2.27
--Example of Description:rownumber, rank, Dense_rank, ntile ranking window functions
-- =============================================
/*1. Preparing data (removing comments and executing--!)
CREATE TABLE T1
(
ID int identity (primary key),
Name varchar (50),
Qty int Default (0)
)
Go
INSERT into T1 (name,qty) select ' A1 ', 1
INSERT into T1 (name,qty) SELECT ' A3 ', 3
INSERT into T1 (name,qty) select ' A2 ', 2
INSERT into T1 (name,qty) Select ' A5 ', 5
INSERT into T1 (name,qty) select ' A2 ', 22
INSERT into T1 (name,qty) select ' A2 ', 21
INSERT into T1 (name,qty) select ' A2 ', 20
INSERT into T1 (name,qty) select ' A1 ', 11
INSERT into T1 (name,qty) select ' A1 ', 12
INSERT into T1 (name,qty) Select ' A5 ', 50
SELECT * FROM t1
*/
/*2.1 Example Over+oder by*/
Select t1.*
, Row_number () over (ORDER by Name,id) as ' row_number '
, Row_number () over (ORDER by name Desc,id desc) as ' Row_number_desc '
, Rank () over (ORDER by name) as ' RANK '
, RANK () over (ORDER by Name,id) as ' rank_name_id '
, Dense_rank () over (ORDER by name) as ' Dense_rank '
, T1.name
, NTILE (1) over (ORDER by Name,id) as ' Ntile_1group '
, NTILE (2) over (ORDER by Name,id) as ' ntile_2g '
, NTILE (3) over (ORDER by Name,id) as ' ntile_3g '
, NTILE (4) over (ORDER by Name,id) as ' ntile_4g '
--ntile (number of packets) when the average is over, the average from the last group is: avg=2 for 4,avg=2 for 3,avg=3 for 2 and 1
from T1
ORDER BY T1.name,id
/*2.2 example over + patition by + ODER by*/
--Add the Patition, will first patition into a plurality of partitions, and then ranked within the partition, the rankings of each district do not interfere with each other, the ranking counter independent starting from 0
Select t1.*
, Row_number () over (PARTITION by name ORDER by Name,id) as ' row_number '
, Row_number () over (PARTITION by name ORDER by name Desc,id desc) as ' Row_number_desc '
, Rank () over (PARTITION by name ORDER by name) as ' rank '
, RANK () over (PARTITION by name ORDER by Name,id) as ' rank_name_id '
, Dense_rank () over (PARTITION by name ORDER by name) as ' Dense_rank '
, T1.name
, NTILE (1) over (PARTITION by name ORDER by Name,id) as ' Ntile_1group '
, NTILE (2) over (PARTITION by name ORDER by Name,id) as ' ntile_2g '
, NTILE (3) over (PARTITION by name ORDER by Name,id) as ' ntile_3g '
, NTILE (4) over (PARTITION by name ORDER by Name,id) as ' ntile_4g '
from T1
ORDER BY T1.name,id
MSSQL Example (iii) ROWNUMBER, rank, Dense_rank, ntile ranking window functions Example
