CTE stands for Common Table Sub Expression.
Its like a view. But its scope is up to that query only. When writing CTE, first we will write a query and will use that query as a table in the same query.
One more version of cte is recursive queries. Some queries which write in sql 2000 using cursors or loops, u can write using recursive CTE.
There are lots of articles on internet.
This function will be used to assign the Unique numbers to the rows with our custom logic.
Eg: Suppose you have a table like below. and u wants to get the top 3 maximum nos for each group.
declare @t table
select 10,15 unionallselect 10,115 unionallselect 10,125 unionallselect 20,145 unionallselect 30,815 unionallselect 20,4315 unionallselect 20,345 unionallselect 30,145 unionallselect 30,768 unionallselect 10,678 unionallselect 10,187 unionallselect 10,098 unionallselect 20,12 unionallselect 30,534 unionallselect 20,235 unionallselect 20,764 unionallselect 30,4535 unionallselect 30,5345
;with cte as
select *,ROW_NUMBER() over (PARTITION by GroupId orderby someVal desc) as rn from @t
select * from cte where rn <= 3
You can see that as we need the Top 3 records for each Group, "group" will go into Partition Clause,
Partition Clause will be used to specify that group combination.