OVER and WITH in t-sql
Determines the partitioning and ordering of the rowset before the associated window function is applied.
Specifies a temporary named result set, known as a common table expression (CTE).
Below code demonstrates the creation of a temperory table and add a field new field which is an ID field of a sorted column
WITH temp_table AS
SELECT *, (ROW_NUMBER() OVER (ORDER BY field_name)) AS row FROM table_name
SELECT * FROM temp_table
The above code can also used for paging featuers . Like MySQL’s LIMIT start, length.
SELECT * FROM temp_table WHERE row BETWEEN 10 AND 15
Ref: Inspired from one of my friend Amit Benswal’s blog post
No related posts.