Basic usage of row_number () over function __ function

Syntax: Row_number () Over (PARTITION by column)

Simply say Row_number () starts with 1, returns a number for each grouped record, where Row_number () over (the order by XLH DESC) first returns a sequence number for the XLH column, followed by a descending order of XLH records.
Example:
XLH row_num
1700 1
1500 2
1085 3
710 4

row_number () over (PARTITION by COL1 Order by COL2) is grouped according to COL1, sorted by COL2 within the group, and the value of this function represents the sequential number within each group that is sorted internally (consecutive unique )

Instance:

Initializing data

CREATE TABLE employee (empid int, deptid int, salary decimal (10,2))
INSERT into employee values (1,10,5500.00)
INSERT into employee values (2,10,4500.00)
INSERT into employee values (3,20,1900.00)
INSERT into employee values (4,20,4800.00)
INSERT into employee values (5,40,6500.00)
INSERT into employee values (6,40,14500.00)
INSERT into employee values (7,40,44500.00)
INSERT into employee values (8,50,6500.00)
INSERT into employee values (9,50,7500.00)

Data is displayed as

Empid DeptID Salary
----------- ----------- ---------------------------------------
1 10 5500.00
2 10 4500.00
3 20 1900.00
4 20 4800.00
5 40 6500.00
6 40 14500.00
7 40 44500.00
8 50 6500.00
9 50 7500.00

Requirements: According to the division, show the salary level of each department

Expected results:

Empid DeptID Salary Rank
----------- ----------- --------------------------------------- --------------------
1 10 5500.00 1
2 10 4500.00 2
4 20 4800.00 1
3 20 1900.00 2
7 40 44500.00 1
6 40 14500.00 2
5 40 6500.00 3
9 50 7500.00 1
8 50 6500.00 2

SQL script:

SELECT *, Row_number () over (partition by DeptID-by-Salary desc) rank from employee

Turn from: http://www.cnblogs.com/digjim/archive/2006/09/20/509344.html

We know that SQL Server 2005 has many new features compared to SQL Server 2005 and SQL Server 2000. In this article we want to discuss a new function row_number (). Database administrators and developers have been looking forward to this function for a long time, and now they are finally waiting.


Typically, developers and administrators compute line numbers in a query using temporary tables and column-related subqueries. SQL Server 2005 now provides a function to generate line numbers instead of all the extra code.

We assume that there is a database [employeetest], there is a table [EMPLOYEE] in the database, you can use the following script to produce the database, table and corresponding data.

Use [MASTER]
Go

IF EXISTS (SELECT NAME from SYS. DATABASES WHERE NAME = N ' employee test ')
DROP DATABASE [employee test]
Go

CREATE DATABASE [EMPLOYEE TEST] Go

Use [EMPLOYEE TEST] Go

IF EXISTS SELECT * from SYS. OBJECTS Here object_id = object_id (N ' [dbo].[ EMPLOYEE] ') and TYPE in (N ' U '))
DROP TABLE [DBO]. [EMPLOYEE]
Go

CREATE TABLE EMPLOYEE (EMPID INT, FNAME VARCHAR (m), LNAME VARCHAR) go

INSERT into employee (EMPID, FNAME, LNAME) VALUES (2021110, ' MICHAEL ', ' Poland ')
inserts into employee (EMPID, FNAME, LNAME) VALUES (2021110, ' MICHAEL ', ' Poland ')
INSERT into EMPLOYEE (EMPID, FNAME, LNAME) VALUES (2021115, ' JIM ', ' KEN Nedy ')
INSERT into employee (EMPID, FNAME, LNAME) VALUES (2121000, ' JAMES ', ' SMITH ')
inserts into employee (EMPID , FNAME, LNAME) VALUES (2011111, ' ADAM ', ' ACKERMAN ')
INSERT into EMPLOYEE (EMPID, FNAME, LNAME) VALUES (3015670, ' MAR THA ', ' Lederer ')
INSERT into EMPLOYEE (EMPID, FNAME, LNAME) VALUES (1021710, ' MARIAH ', ' Mandez ')

We can use the following script to query the employee table.

SELECT EMPID, Rname, LNAME from EMPLOYEE

The results of this query should look like Figure 1.0

2021110	MICHAEL	Poland
2021110	MICHAEL	Poland
2021115	JIM	KENNEDY
2121000	JAMES	SMITH
2011111	ADAM	ACKERMAN
3015670	MARTHA	Lederer
1021710	MARIAH	Mandez

Figure 1.0

In SQL Server 2005, to generate line numbers from the data in this table, I usually use the following query.

SELECT rowid=identity (int,1,1), EMPID, FNAME, LNAME into EMPLOYEE2 from EMPLOYEE order by EMPID

This query creates a new table with the identify function to produce the line number. We use the following query to look at the data for this table.

SELECT ROWID, EMPID, FNAME, LNAME from EMPLOYEE2

The results of the query above are shown in Figure 1.1

1	1021710	MARIAH	Mandez
2	2011111	ADAM	ACKERMAN
3	2021110	MICHAEL	Poland
4	2021110	MICHAEL	Poland
5	2021115	JIM	KENNEDY
6	2121000	JAMES	SMITH
7	3015670	MARTHA	Lederer

Figure 1.1

The results of this query are obvious emp=2021110 rows are duplicate data.

To delete empid=2021110 duplicate data, we must delete it in the EMPLOYEE2 table and not directly in the employee.

SQL Server 2005 provides a new function (Row_number ()) to produce line numbers. We can use this new function to delete the duplicate data from the original table and add the Row_number () function to the usual expression.

Let's use the Row_number () function to generate ROWID based on the empid.

SELECT row_number (EMPID ASC) as ROWID, * from EMPLOYEE

The results of the query above are shown in Figure 1.2

1	1021710	MARIAH	Mandez
2	2011111	ADAM	ACKERMAN
3	2021110	MICHAEL	Poland
4	2021110	MICHAEL	Poland
5	2021115	JIM	KENNEDY
6	2121000	JAMES	SMITH
7	3015670	MARTHA	Lederer

Figure 1.2

In this result, we can distinguish empid from 2021110 of duplicate data.

We can use the common table query expression and the Row_numner () function to select the duplicate row of data.

With [EMPLOYEE ORDERED by ROWID] as
(SELECT row_number () over (order by EMPID ASC) as ROWID, * from EMPLOYEE)
SELECT * FROM [EMPLOYEE ORDERED by ROWID] WHERE ROWID =4

The results of the query above are shown in Figure 1.3

4

2021110

MICHAEL

Poland

Figure 1.3

This line of duplicate data can be removed using the common table and the Row_number () function below.

With [EMPLOYEE ORDERED by ROWID] as
(SELECT row_number () over (order by EMPID ASC) as ROWID, * from EMPLOYEE)
DELETE from [EMPLOYEE ORDERED by ROWID] WHERE ROWID =4

After the deletion, we can use the following query statement to look at the results.

SELECT * from EMPLOYEE

The results of this query are shown in Figure 1.4

2021110	MICHAEL	Poland
2021115	JIM	KENNEDY
2121000	JAMES	SMITH
2011111	ADAM	ACKERMAN
3015670	MARTHA	Lederer
1021710	MARIAH	Mandez

Figure 1.4

Here we can see that the duplicate data has been deleted.

Summarize

In this article, we discussed the new feature row_number () function for SQL Server 2005, as well as the usual table expressions, and then how to use both to remove duplicate rows.