Why do we need `order by` in row_number() over(order by column name)?

Question

Why we should use "order by" in over phrase in this t-sql term:

row_number() over(order by column name)

Answer 1

If you are asking why it is mandatory to place the ORDER BY clause in the OVER CLAUSE (other than just because the ANSI spec requires it), which you may consider to be redundant as it is in addition to the overall ORDER BY clause which would be placed at the end of the query.

The usefulness of the ORDER BY in the OVER() clause is because you may wish to apply a different ORDER BY to the overall query, than the ordering applied when determining the column generated by the partitioned ROW_NUMBER() function. Consider the following query, where we want to apply a ranking to the population of a city within (PARTITION BY) of the city, but ordered alphabetically by country name and city name:

Assuming City has (CountryName, CityName, Population)

select CountryName, CityName, 
       ROW_NUMBER() OVER (PARTITION BY CountryName 
                          ORDER BY Population DESC) AS RankedPopulation
FROM City
ORDER BY CountryName, CityName;

Which could present a result like so:

CountryName     CityName       RankedPopulation
--------------- -------------- -----------------
England         Liverpool      2
England         London         1
France          Lyons          2
France          Paris          1
USA             Los Angeles    2
USA             Miami          3
USA             New York       1

If you have multiple columns projected from the analytic functions, each of these can have independent orderings in the respective OVER clauses.

Note in my example above, RANK would be a more appropriate choice than ROW_NUMBER - I just wanted to retain the context of your question.

SqlFiddle of the example here