The use of row_number Function)

1. Used to delete duplicate records (the use of to delete the common record)

Example: (example)

#1 initialize data (initialize the data)

Create Table # tmp1 (ID int, name nvarchar (20), age INT );

Insert into # tmp1 values (1, 'cangowu', 25)

Insert into # tmp1 values (2, 'cangowu', 25)

Insert into # tmp1 values (3, 'cangowu', 25)

Insert into # tmp1 values (4, 'ajay ', 29)

Insert into # tmp1 values (5, 'ajaje', 29)

Insert into # tmp1 values (6, 'ajaje', 29)

#2 operation SQL (T-SQL)

-- Before the operation (before the operation)

Select * from # tmp1

 

ID name age
1 cangowu 25
2 cangowu 25
3 cangowu 25
4 Ajay 29
5 Ajay 29
6 Ajay 29

-- After the operation)

Select name, age from
(
Select *, row_number () over (partition by name order by ID) Row from # tmp1
) TMP
Where ROW = 1

 

Name age
Ajay 29
Cangowu 25

2. The record fields are not repeatedly displayed (the record fields repeat same display)

Example: (example)

#1 initialize data (initialize the data)

Create Table # tmp2 (ID int, name nvarchar (20), age int, month int, salary INT );

Insert into # tmp2 values (1, 'cangowu', 2500)

Insert into # tmp2 values (2, 'cangowu', 2500)

Insert into # tmp2 values (3, 'cangowu', 2500)

Insert into # tmp2 values (4, 'cangowu', 2500)

Insert into # tmp2 values (5, 'cangowu', 2500)

Insert into # tmp2 values (6, 'cangowu', 2500)

Insert into # tmp2 values (11, 'preston ', 35, 1, 8000)

Insert into # tmp2 values (12, 'preston ', 35,2, 8000)

Insert into # tmp2 values (13, 'preston ', 35,3, 8000)

Insert into # tmp2 values (14, 'preston ', 35,4, 8000)

Insert into # tmp2 values (15, 'preston ', 35, 5, 8000)

Insert into # tmp2 values (16, 'preston ', 35, 6, 8000)

Insert into # tmp2 values (21, 'ajay ', 5000)

Insert into # tmp2 values (22, 'ajay ', 5000)

Insert into # tmp2 values (23, 'ajay ', 5000)

Insert into # tmp2 values (24, 'ajay ', 5000)

Insert into # tmp2 values (25, 'ajay ', 5000)

Insert into # tmp2 values (26, 'ajay ', 5000)

#2 operation SQL (T-SQL)

Select
Name = (case when ROW = 1 then name else ''end)
, Age = (case when ROW = 1 then age else null end)
, Month
, Salary
From
(
Select *, row_number () over (partition by name order by ID) Row from # tmp2
) TMP

 

Name age month salary
Ajay 29 1 5000
2 5000
3 5000
4 5000
5 5000
6 5000
Cangowu 25 1 2500
2 2500
3 2500
4 2500
5 2500
6 2500
Preston 35 1 8000
2 8000
3 8000
4 8000
5 8000
6 8000

 

The use of row_number Function)