asked on

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Query that MUST use both where and order by clause
I am getting this ERROR
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
            WITH UKCustomers3       (      CustID, CustName,CustEmail,Gender,CustHireDate,CustSales)

            AS
            --We define the CTE QUERY

            (      SELECT      CustID,CustName,CustEmail,Gender,CustHireDate,CustSales
                  FROM      dbo.UKCustomers3
            )

            ,CTE_UKCustomers3

            AS

            --Define the Outer Query Referencing CTE_UKCustomers3
            --To Find out the TOP CustSales by Gender = Becky

            (SELECT       *
                  FROM      UKCustomers3
                  WHERE      Gender = 'F'
                  ORDER BY CustSales
            )

            -- To verify

            SELECT * FROM @UKCustomers3

Fred

ASKER

custid Custname Gender date hired Salary
5        Becky      BT@yahoo.com        F        2008-03-08 00:00:00.000        65000.00
4        Yolanda      yl@gmail.com      F        2019-03-03 00:00:00.000        35000.00
2        Suesl@yah.com        F        2008-02-02 00:00:00.000        15000.00

Fred

ASKER

Sample Out put, I was trying to use both where and order by clause on the above table then create a CTE table

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fred

ASKER

is this the same case with Group BY/Having Clause in CTE, ie they have to be on the outer query too ?

ste5an

No. Cause a GROUP BY modifies the set. Only the ORDER BY must be in the outer query. The idea is. that a ORDER BY in a inner query can be always useless, when the consumer adds an ORDER BY in his outer query. Thus it's not permitted, which on the otherhand allows faster query compilation.