Home » SQL Server

Samples and learning artifacts on CTE


Can some one point me to links that give basic understanding on CTE and provides inputs on when to use ROW_NUMBER() and over (partition)?



4 Answers Found


Answer 2

In addition to Uri's response check

CTE and hierarchical queries

This blog has links  to several other good articles on that topic.


Answer 3

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.




Row_Number :

    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
	groupId int,
	Someval int

insertinto @t 
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.


Answer 4

Thanks folks for your valuable answers :)




<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure