Common Table Expression(CTE) and ROW_NUMBER()

can be thought of as a temporary result set that is defined with in the execution of the scope of a single select,insert,update,delete or create view statement.

A CTE can be used to :

• create a recursive query .
• substitute for a view when the general use of a view is not required ,that is you dont have to store the definition in metadata
• enable grouping by a column that is derived from a scalar subset ,or a function that is not deterministic or has extrenal access.
• reference the resulting table multiple time in the same statement.

Syntax

with expression_name [(column name [,....n])]
AS
(CTE query definition)

E.g :with [EMP_SCHED order by rowid] as
(select row_number() over (order by emp_id asc) as rowid ,* from EMP_SCHED)
select * from [EMP_SCHED order by rowid] where rowid=3

Row_Number()

Returns the sequential number of a row with in a partition of a result set ,starting at 1 for the first in each partition

Syntax
ROW_NUMBER() OVER ([ ] )

E.g :with [EMP_SCHED order by rowid] as
(select row_number() over (order by emp_id asc) as rowid ,* from EMP_SCHED)
select * from [EMP_SCHED order by rowid] where rowid=3