Row_number ()/rank ()/dense_rank ()/ntile () over ()

Today women vote asked me SQL Server four sort, on the spot wrote a few words SQL let her understand, now put related SQL put up.

First, we create some test data.

If object_id (' tempdb.dbo. #Tmp ') is not a null    drop table #Tmpcreate table #Tmp (name nvarchar) insert INTO #Tmpselect N ' Zhang San ' unionselect N ' John Doe ' UNION select n ' Harry ' union select n ' Zhao Liu ' unionselect n ' Zhu Seven ' unionselect n ' King VIII ' union allselect N ' Zhang San '

The last Union is the union all, because we have more than one line of "Zhang San".

One, Row_number () over (partition by ColumnName ORDER by ColumnName)

Select Row_number () over (order by name) as num,* from #Tmp

You can get a result set sorted by name.

Row_number () over () also has a usage that can be grouped for a column.

The following results show that Zhang San has 1 and 22 sorts, while the other names are sorted by only 1.

Select Row_number () over (partition by name, order by name) as num,* from #Tmp

RANK () over (order by ColumnName)

You can see from the result set below that the result set is numbered 5 less, and has two 4 numbers and jumps directly to number 6.

Select RANK () over [order by name], * from #Tmp

Third, Dense_rank () over (order by ColumnName)

Select Dense_rank () over (order by name), * from #Tmp

After executing the SQL, it is found that the following result set has 2 numbered 4 lines followed by the number 5 lines.

The Dense_rank () function is similar to the RANK () function.

The RANK () function is divided into groups, and the final number must be the same as the number of rows.

The last number of the Dense_rank () function is related to the number of groupings.

Iv. NTILE () over (ORDER by COLUMNNAME)

Select NTILE (2) over (order by name), * from #Tmpselect NTILE (3) through (order by name), * from #Tmp

The number behind the ntile is to divide the results of the query evenly into groups.
As divided into 2 and 3 groups.
If the number of rows is divided evenly and there are Yu Xing, then the rows are divided into the first groups.
For example, we have 7 rows of results to be divided into 3 groups.
Then the first group of 3 rows, the second group 2 rows, the third group 2 rows.
If we result in 14 rows, the average is divided into 3 groups.
Then the first group of 5 rows, the second group 5 rows, the third group 4 rows.
And so on

Category: "SQL Server:t-sql Technology Insider Series"

Row_number ()/rank ()/dense_rank ()/ntile () over ()