The SQL2005 ROW_NUMBER () function usage

2005 than in 2000 added a few functions, is the ROW_NUMBER (), rank () dense_rank, (), ntile (takes) The following examples briefly explain.

create table gg (sname varchar (10), sort varchar (10), num int)

go

 

insert into gg

select ‘White peony root’, ‘root vegetables’, 55

union all

select ‘Pinellia law’, ‘tubers’, 78

union all

select ‘Bupleurum’, ‘tubers’, 60

union all

select ‘Chuanxiong’, ‘tubers’, 99

union all

select ‘Days censer’, ‘grass’, 68

union all

select ‘Rushes’, ‘grass’, 55

union all

select ‘Solanum nigrum’, ‘grass’, 60

union all

select ‘Iwami wear’, ‘grass’, 60

union all

select ‘Nepenthes’, ‘grass’, 70

union all

select ‘Motherwort’, ‘grass’, 86

union all

select ‘Lentils’, ‘fruits’, 86

union all

select ‘Tsaoko’, ‘fruits’, 70

union all

select ‘Rosa laevigata’, ‘fruits’, 55

union all

select ‘Ligustrum lucidum’, ‘fruits’, 94

union all

select ‘Panda Hai’, ‘fruits’, 66

union all

select ‘Mulberry’, ‘fruits’, 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 a small to large num one by one rank, not side by side, in continuous

– RANK () num Ascending rank one by one, side by side, the ranking is not continuous

– DENSE_RANK () one by one num Ascending ranked tied for fourth consecutive

– Ntile (takes) num small to big into groups one by one ranking tied for fourth consecutive

 

sname       sort        num        rownum         ranknum        dersenum        ntilenum
————————————————– ——————————–
Root of herbaceous peony          Tubers        55           1           1              1                1
Rush        Grasses          55           2           1              1                1
Jinyingzi        Fruits        55           3           1              1                1
Solanum nigrum          Grasses          60           4           4              2                1
Stone see through        Grasses          60           5           4              2                1
Bupleurum          Tubers        60           6           4              2                1
Panda Hai        Fruits        66           7           7              3                2
Days censer        Grasses          68           8           8              4                2
Tsaoko          Fruits        70           9           9              5                2
Nepenthes        Grasses          70           10          9              5                2
France Pinellia        Tubers        78           11          11             6                2
Mulberry          Fruits        78           12          11             6                3
Motherwort        Grasses          86           13          13             7                3
Hyacinth bean          Fruits        86           14          13             7                3
Ligustrum lucidum        Fruits        94           15          15             8                3
Chuanxiong          Tubers        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 add the partition by sort by category classified ntile (takes 3) means that the force divided into three groups.

 

sname       sort            num        rownum         ranknum         dersenum        ntilenum
——————    ————————————————– ————–
Rush        Grasses           55           1             1                1                1
Solanum nigrum          Grasses           60           2             2                2                1
Stone see through        Grasses           60           3             2                2                2
Days censer        Grasses           68           4             4                3                2
Nepenthes        Grasses           70           5             5                4                3
Motherwort        Grasses           86           6             6                5                3
Root of herbaceous peony          Tubers         55           1             1                1                1
Bupleurum          Tubers         60           2             2                2                1
France Pinellia        Tubers         78           3             3                3                2
Chuanxiong          Tubers         99           4             4                4                3
Jinyingzi        Fruits         55           1             1                1                1
Panda Hai        Fruits         66           2             2                2                1
Tsaoko          Fruits         70           3             3                3                2
Mulberry          Fruits         78           4             4                4                2
Hyacinth bean          Fruits         86           5             5                5                3
Ligustrum lucidum        Fruits         94           6             6                6                3

(16 rows affected)

 

The following SQL 2000 to achieve, compared to 2005 to more trouble.

– ROW_NUMBER in SQL 2000 in the implementation of

– The use of temporary tables and Identity (function)

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

 

– RANK achieve 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

 

– The in sql DENSE_RANK of the 2000 in the realization of

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 # tt

on r. num = t. num

order by num

 

drop table # t

Posted by databasesql