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:
- ·specifies the numbering, which will be different from the sort order of rows in the result set;
- ·create a “non-through” numbering, ie select group of the common set of rows and number them separately for each group;
- ·use several methods numbering as in fact is independent of the numbering collation string query.
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:
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: