Common Table Expression - MS SQL

Sometimes we need to do some processes over temporaray data just like a View.

A CTE (Common Table Expression) is the best choice for the same.

Common Table Expressions, or CTE, are a new construct introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query. Moreover, CTEs can be recursively defined, allowing a recursive entity to be enumerated without the need for recursive stored procedures.

A simple example

with demo as (
SELECT col1, col2, col2,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3) as 'RowNumber'
FROM DBO..tablename
)
Select bpt from dbo..tablename bpt where bpt.col2 in (select col2 from demo where RowNumber > 1)


There are 3 things 1)CTE 2) ROW_NUMBER() 3)PARTITION BY

Here 'demo' is the CTE name. The thing to remember is CTE only applies to the first query following it.


The rank functions are as follows

Row_number(): It will produce row number of each rows on the basis of a column value.
example:

Rank(): will produce the (list of) ranks on the basis of a column value. this will leave gap between groups of same values.

Dense_rank() this is same as rank but it will not leave that gap like rank().

Ntile(n): It will partition the table into n number of parts.

Partition will basically create a partition on the column name.