The use of functions such as row_number () in SQL2005

The use of functions such as row_number () in SQL2005
Today just loaded SQL Server 2005, feel OK, 2005:2000 new functions, namely row_number (), rank (), Dense_rank (), Ntile (), the following examples are explained briefly.

CREATE TABLE GG (sname varchar), sort varchar (ten), Num int)
Go

INSERT INTO GG
Select ' Radix paeoniae alba ', ' rhizome ', 55
UNION ALL
Select ' Pinellia ', ' rhizome class ', 78
UNION ALL
Select ' Bupleurum ', ' rhizome class ', 60
UNION ALL
Select ' Chuanxiong ', ' Rhizome class ', 99
UNION ALL
Select ' Day censer ', ' Grass class ', 68
UNION ALL
Select ' Rushes ', ' grass class ', 55
UNION ALL
Select ' Solanum nigrum ', ' Grass class ', 60
UNION ALL
Select ' Stone see wear ', ' grass class ', 60
UNION ALL
Select ' Nepenthes ', ' grass ', 70
UNION ALL
Select ' Motherwort ', ' Grass class ', 86
UNION ALL
Select ' Lentils ', ' fruit class ', 86
UNION ALL
Select ' Tsaoko ', ' fruit class ', 70
UNION ALL
Select ' Rosa laevigata michx ', ' Fruit class ', 55
UNION ALL
Select ' Privet ', ' Fruit class ', 94
UNION ALL
Select ' Fat Sea ', ' fruit class ', 66
UNION ALL
Select ' Mulberry mulberry ', ' Fruit class ', 78

Select Sname,sort,num,
Row_number () Over (order by Num) as RowNum,
Rank () Over (order by Num) as Ranknum,
Dense_rank () Over (order by Num) as Dersenum,
Ntile (3) over (order by num) as Ntilenum
from GG

-Results
--row_number () is ranked by Num from small to large, not tied, ranked consecutively
--rank () is ranked by Num from small to large, tied, ranked discontinuous
--dense_rank () is ranked by Num from small to large, tied, ranked consecutively
--ntile () is ranked by Num from small to large in groups, tied, ranked consecutively



sname sort num rownum ranknum dersenum ntilenum
-------- --------- --------- ------------- ------------- --------------- ---------------
Rhizome of Radix paeoniae Alba 55 1 1 1 1
Rushes 55 2 1 1 1
Rosa laevigata Michx Fruit Class 55 3 1 1 1
Solanum nigrum Grass Category 60 4 4 2 1
Stone see-Through Grass 60 5 4 2 1
Bupleurum Root Class 60 6 4 2 1
Fat Sea Fruit Category 66 7 7 3 2
Day Censer Grass Class 68 8 8 4 2
Tsaoko Fruit Class 70 9 9 5 2
Nepenthes 70 10 9 5 2
Rhizome of Pinellia Ternate 78 11 11 6 2
Mulberry Mulberry Fruit Class 78 12 11 6 3
Motherwort Grass Class 86 13 13 7 3
Lentils Fruit Class 86 14 13 7 3
Privet Fruit Class 94 15 15 8 3
Rhizome of Chuanxiong 99 16 16 9 3
(16 rows affected)

Select Sname,sort,num,
Row_number () over (partition by sort order by num) as RowNum,
Rank () over (partition by sort order by num) as Ranknum,
Dense_rank () over (partition by sort order by num) as Dersenum,
Ntile (3) over (partition by sort order by num) as Ntilenum
from GG

-Results
At this point, partition by sort is sorted by category, and Ntile (3) means that it is forced into three groups.

sname sort num rownum ranknum dersenum ntilenum
-------- ---------- --------- ------------- --------------- ---------------- -----------
Rushes 55 1 1 1 1
Solanum nigrum Grass Category 60 2 2 2 1
Stone see-through Grass 60 3 2 2 2
Day Censer Grass Class 68 4 4 3 2
Nepenthes 70 5 5 4 3
Motherwort Grass Class 86 6 6 5 3
Rhizome of Radix paeoniae Alba 55 1 1 1 1
Bupleurum Root Class 60 2 2 2 1
Rhizome of Pinellia Ternate 78 3 3 3 2
Rhizome of Chuanxiong 99 4 4 4 3
Rosa laevigata Michx Fruit Class 55 1 1 1 1
Fat Sea Fruit Category 66 2 2 2 1
Tsaoko Fruit Class 70 3 3 3 2
Mulberry Mulberry Fruit Class 78 4 4 4 2
Lentils Fruit Class 86 5 5 5 3
Privet Fruit Class 94 6 6 6 3
(16 rows affected)

The following are implemented with SQL 2000, which is more cumbersome than 2005.
Implementation of--row_number in SQL 2000
--using temporal tables and identity (functions)
Select Sname,num,identity (int,1,1) as RowNumber
Into #tem
from GG
ORDER BY num

Select Sname,num,rownumber
From #tem

drop table #tem
Go

Implementation of--rank in SQL 2000
Select Sname,num,
(select COUNT (1) +1 from GG where num<g.num) as Ranknum
from GG G
ORDER BY num
Go

Implementation of--dense_rank in SQL 2000
Select Num,identity (int,1,1) as Densenum
Into #t
from GG
GROUP BY Num
ORDER BY num

Select R.sname,r.num,t.densenum
From GG R join #t t
On R.num=t.num
ORDER BY num

drop table #t
Go

The use of functions such as row_number () in SQL2005