/* difference between four ranking functions: Rank () returns the rank of each row within the partition of the result set. The rank of the row is the number of positions before the related row plus one. If two or more lines are associated with a rank, each association row gets the same rank. Dense_rank () returns the rank of the row in the result set partition without any interruption in the ranking. The row is ranked equal to the number of all positions before the row discussed plus one. If two or more rows are constrained by the rank in the same partition, each constraint row receives the same rank. Row_number () returns the serial number of the expert in the result set partition, starting with the first row of each partition. An ORDER BY clause determines the order in which the rows are assigned unique row_number in a particular partition. NTILE () distributes the rows in an ordered partition to a specified number of groups. Each group has a number, numbering from the beginning. For each row, NTILE returns the number of the group to which this row belongs. */--Create test data table declare @table table (name VARCHAR (4), score INT) insert into @tableSELECT ' Zhang San ', 129 UNION allselect ' John Doe ', 137 Unio N Allselect ' King II ', 137 Union allselect ' xiaoming ', 126 union allselect ' small Six ', 126 union allselect ' small white ', Union allselect ' Little Black ', 124 U Nion allselect ' West Big ', 123 Union allselect ' small just ', ' Union allselect ' senior ', Union Allselect ' Tang Dynasty ', Union Allselect ' ', 99 --Take a look at select * from @table select name, score, row_number () over (ORDER by result DESC) as [Row_number], RANK () Over (order by result desc) as [RANK], Dense_rank () over (order by result desc) as [Dense_rank], NTILE (3) over ( ORDER by result DESC) as [Ntile]from @table
SQL four ranking function differences