• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Analytic function in sql

 
Guddu Ali
Ranch Hand
Posts: 40
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SELECT ROW_NUMBER() OVER(ORDER BY HIREDATE) AS ROW_NO, ENAME,HIREDATE,DEPNO FROM EMP;

As above query which clause will execute first ie ROW_NUMBER() or ORDER BY HIREDATE ?
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The row number is assigned based on the order by statement, so order by is handled first.

From the 11.2 docs:

"
ROW_NUMBER assigns each row a distinct value even if there is a tie based on the order_by_clause. The value is based on the order in which the row is processed, which may be nondeterministic if the ORDER BY does not guarantee a total ordering.
"
 
Guddu Ali
Ranch Hand
Posts: 40
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But I think last set of operations performed in a query is always be ORDER BY clause then how order by is handled first ?
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Guddu Ali wrote:But I think last set of operations performed in a query is always be ORDER BY clause then how order by is handled first ?


Because, as the documentation says, row_number needs the final order of rows.
So has to be done after ORDER BY.

It's possible SQL Server is different, but you haven't said which DB you were using.

In any case, not sure where you go the idea that ORDER BY is the last thing performed.
Several analytic functions use the order to determine their output.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Guddu Ali wrote:But I think last set of operations performed in a query is always be ORDER BY clause then how order by is handled first ?

It's a bit confusing but there are effectively two "ORDER BY" clauses in SQL:
This is the traditional ORDER BY, which sorts the data after it has been queried, as you say.

The other ORDER BY is actually part of the ROW_NUMBER() analytical function:
In this case, you have said you want to generate a ROW_NUMBER and you want to sort the data OVER(ORDER BY...), so this "ORDER BY" is applied during the query process to generate the ROW_NUMBER in the required order.

It might be less confusing if the ROW_NUMBER() function contained the ORDER BY e.g. ROW_NUMBER(ORDER BY hiredate), but this would not be consistent with many other analytical functions that have the same pattern of DO_SOMETHING(...) OVER(...).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic