NinethSense

OVER Clause

Determines the partitioning and ordering of the rowset before the associated window function is applied.
URL: http://msdn2.microsoft.com/en-us/library/ms189461.aspx

WITH

Specifies a temporary named result set, known as a common table expression (CTE).
URL: http://msdn2.microsoft.com/en-us/library/ms175972.aspx

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


Useful, right?

Ref: Inspired from one of my friend Amit Benswal’s blog post

No related posts.