RSS

Monthly Archives: July 2009

Recursive using Common Table Expressions (CTE)

Introduction

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to

  • Create a recursive query (Current Example)
  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement.

    Syntax of CTE

    with CTE_Name(Col1,Col2,Col3..)
    as (
    –Anchor Query
    Union All
    –Recursive member is defined referencing cte_name
    )
    Select * From CTE_Name

    Example

    use AdventureWorks

    go

     

    with CTE_EmployeeLevel(EmployeeId,FullName,ManagerId,ManagerName,EmpLevel)

    as

    (

    select

    EmployeeID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= EmployeeID)FullName,

    ManagerID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= ManagerID)ManagerName,

    1

    From HumanResources.Employee

    where ManagerId Is Null

     

    union all

     

    Select

    tbEmp.EmployeeID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= tbEmp.EmployeeID)FullName,

    tbEmp.ManagerID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= tbEmp.ManagerID)ManagerName,

    EmpLevel+1

    From HumanResources.Employee tbEmp inner join CTE_EmployeeLevel CTEEmp

    on tbEmp.ManagerID=CTEEmp.EmployeeId

    )

     

    Select * From CTE_EmployeeLevel

    Exam
    ple Code Walkthrough

    when Anchor query run the result set will be

    EmployeeId EmployeeName ManagerId ManagerName EmpLevel
    Stephanie Bourne 109 NULL NULL 1

    then We will take Employee Id from above result set then join it with employee id from Employee table

    Then the result will be

    EmployeeId EmployeeName ManagerId ManagerName EmpLevel
    6 Frances Adams 109 Stephanie Bourne 2

    we will repeat step before again Then We will take Employee Id from above result set then join it with employee id from Employee table

    Then the result will be

    EmployeeId EmployeeName ManagerId ManagerName EmpLevel
    2 Catherine Abel 6 Frances Adams 3
    46 Stephen Ayers 6 Frances Adams 3
    106 Corinna Bolender 6 Frances Adams 3
    119 Christopher Bright 6 Frances Adams 3

    we will repeat step before again Then we will take Employee Id from above result set then join employee id from Employee if no result

    CTE start join all result set to be

    EmployeeId EmployeeName ManagerId ManagerName EmpLevel
    Stephanie Bourne 109 NULL NULL 1
    6 Frances Adams 109 Stephanie Bourne 2
    2 Catherine Abel 6 Frances Adams 3
    46 Stephen Ayers 6 Frances Adams 3
    106 Corinna Bolender 6 Frances Adams 3
    119 Christopher Bright 6 Frances Adams 3
  •  

    Tags: , , ,

    Ranking Functions

    Introduction

    Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.

    Transact-SQL provides the following ranking functions:

    RANK :The rank of a row is one plus the number of ranks that come before the row in question.

    NTILE :The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

    DENSE_RANK :The rank of a row is one plus the number of distinct ranks that come before the row in question.

    ROW_NUMBER : Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

    Example

     

    USE AdventureWorks;

    GO

    SELECT c.FirstName, c.LastName

          –Each Row Take row number based on postal code

        ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS ‘Row Number’

        –Each same postalcode take same rank then next postalcode take (Total count of same PostalCode rows)+1

        ,RANK() OVER (ORDER BY a.PostalCode) AS ‘Rank’

        –Each same postalcode take same rank then next postalcode take rank+1

        ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS ‘Dense Rank’

        –divide Total Result Sets to 4 groups

        ,NTILE(4) OVER (ORDER BY a.PostalCode) AS ‘Quartile’

        ,s.SalesYTD, a.PostalCode

    FROM Sales.SalesPerson s

        INNER JOIN Person.Contact c

            ON s.SalesPersonID = c.ContactID

        INNER JOIN Person.Address a

            ON a.AddressID = c.ContactID

    WHERE TerritoryID IS NOT NULL

        AND SalesYTD <> 0;

    result will be

    FirstName LastName Row Number Rank Dense Rank Quartile SalesYTD P
    ostalCode
    Maciej Dusza 1 1 1 1 4557045 98027
    Shelley Dyck 2 1 1 1 5200475 98027
    Linda Ecoffey 3 1 1 1 3857164 98027
    Carla Eldridge 4 1 1 1 1764939 98027
    Carol Elliott 5 1 1 2 2811013 98027
    Jauna Elson 6 6 2 2 3018725 98055
    Michael Emanuel 7 6 2 2 3189356 98055
    Terry Eminhizer 8 6 2 3 3587378 98055
    Gail Erickson 9 6 2 3 5015682 98055
    Mark Erickson 10 6 2 3 3827950 98055
    Martha Espinoza 11 6 2 4 1931620 98055
    Janeth Esteves 12 6 2 4 2241204 98055
    Twanna Evans 13 6 2 4 1758386 98055
     

    Tags: , , , ,

     
    %d bloggers like this: