Database consultancy: ROW_NUMBER() function

ROW_NUMBER function numbers the rows returned by the query. You can use it to perform more complex ordering of rows in the report than the one given by the ORDER BY clause.
Using the ROW_NUMBER you can:

The easiest way possible the ROW_NUMBER show on simple examples, to which we now turn. Firstly, we will create table and insert into table some values:

-- USE master
GO

DECLARE@Cities TABLE(CityID int
, Country nvarchar(100)
, City nvarchar(100))

INSERT INTO@Cities (CityID, Country, City)
VALUES( 1, 'USA', N'New York'), ( 2, 'USA', N'Miami'), ( 3, 'USA', N'Chicago')
, ( 4, 'CANADA', N'Toronto'), ( 5, 'CANADA', N'Ottawa'), ( 6, 'CANADA', N'Montreal')
, ( 7, 'FRANCE', N'Paris'), ( 8, 'FRANCE', N'Lyon'), ( 9, 'FRANCE', N'Marseille')
GO  

Now we can use function ROW_NUMBER in SELECT statement:

 SELECT ROW_NUMBER() OVER(ORDER BY City) as NUM
, CityID
, City
FROM @Cities
WHERE Country = 'USA'
ORDER BY CityID

Offer OVER, which is used ROW_NUMBER function sets the numbering of the lines. This uses additional sentence ORDER BY, which is not related to the order of query string output. If you look at the result, you’ll notice that the order of rows in the result set, and the numbering is not the same:

1

And if you want to add numbering flights for each company separately? To do this, we will need another design in the proposal OVER – PARTITION BY. Design PARTITION BY sets of lines for which the independent numbering. The group is defined by the values ??in the column list specified in this design, with lines that make up the group.

SELECT ROW_NUMBER() OVER(PARTITION BY Country ORDER BY CityID) as NUM
, CityID
, City
FROM @Cities
ORDER BY CityID

PARTITION BY Country means of each country form a group for which the numbering is performed independent. As a result, we get:

2