none
Basic doubt in CTE

    Question

  • Hi all,

       Learning CTE in deep, I encountered the sentence "Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access" What is the exact issue of CTE here,How CTE applicable to above sentence.

    Pls guide me.

    Thanks

    Saturday, August 02, 2014 4:36 AM

Answers

  • "Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access" This means that

    You can use the CTE for grouping the data using a column that is being derived using a function

    example -

    WITH CTE (Col1 , col2 , col3 )
    as
    (Select 1, 1, 1+1
    )
    SELECT MAX(col1),Max(Col2) FROM CTE
    GROUP BY Col3


    Regards Keerthi Kiran Hope this answered your question

    Saturday, August 02, 2014 6:46 AM
  • Here is the example to overcome the non-deterministic limitation of GROUP BY:

    SELECT getdate() as DateX, Max(ListPrice) FROM Production.Product
    GROUP BY getdate();
    GO
    /*
    Msg 164, Level 15, State 1, Line 2
    Each GROUP BY expression must contain at least one column that is not an outer reference.
    */
    
    WITH CTE AS (SELECT getdate() as DateX, ListPrice FROM Production.Product)
    SELECT DateX, Max(ListPrice)
    FROM CTE GROUP BY DateX;
    -- 2014-08-02 19:27:04.300	3578.27


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Saturday, August 02, 2014 11:28 PM
    Moderator

All replies

  • Can you give some context. Where did you see the above sentence?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, August 02, 2014 6:07 AM
  • here sir

    CTE

    3rd point.

    Saturday, August 02, 2014 6:17 AM
  • "Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access" This means that

    You can use the CTE for grouping the data using a column that is being derived using a function

    example -

    WITH CTE (Col1 , col2 , col3 )
    as
    (Select 1, 1, 1+1
    )
    SELECT MAX(col1),Max(Col2) FROM CTE
    GROUP BY Col3


    Regards Keerthi Kiran Hope this answered your question

    Saturday, August 02, 2014 6:46 AM
  • and by non deterministic it means you can use functions like GETDATE() etc to derive the values of columns inside CTE

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, August 02, 2014 7:26 AM
  • Here is the example to overcome the non-deterministic limitation of GROUP BY:

    SELECT getdate() as DateX, Max(ListPrice) FROM Production.Product
    GROUP BY getdate();
    GO
    /*
    Msg 164, Level 15, State 1, Line 2
    Each GROUP BY expression must contain at least one column that is not an outer reference.
    */
    
    WITH CTE AS (SELECT getdate() as DateX, ListPrice FROM Production.Product)
    SELECT DateX, Max(ListPrice)
    FROM CTE GROUP BY DateX;
    -- 2014-08-02 19:27:04.300	3578.27


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012








    Saturday, August 02, 2014 11:28 PM
    Moderator