1

I'm trying to run the following query and I'm getting this error:

Msg 1033, Level 15, State 1, Line 14
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.

Here is the query:

WITH cbt_users AS 
(
    SELECT
        mo, eid, rate, cat, quantity AS cbt_hours
    FROM
        crew.crew_rates
    WHERE
        rate_type = 'HOMSTUDY_HRS'
),
tot_hrs AS 
(
    SELECT
        mo, eid, rate, quantity AS tot_hrs
    FROM
        crew.crew_rates
    WHERE
        rate_type = 'TOT HRS'
),
GU_cbt_compare AS 
(
    SELECT
        cbt_users.mo, cbt_users.eid, cbt_users.rate, cbt_hours, tot_hrs,
        CASE WHEN cbt_users.cat = 'REG' THEN 72 ELSE 75 END AS min_GU
    FROM
        cbt_users 
    LEFT JOIN 
        tot_hrs ON cbt_users.mo = tot_hrs.mo AND cbt_users.eid = tot_hrs.eid
    ORDER BY 
        cbt_users.mo, cbt_users.eid
)
SELECT
    mo, eid, rate, cbt_hours, tot_hrs, min_GU
FROM
    GU_cbt_compare
ORDER BY 
    mo, eid
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Cunha
  • 11
  • 1
  • 8
    You have an order by clause in your GU_cbt_compare cte, remove it. The error message told you the problem. – Sean Lange Apr 12 '19 at 16:34
  • Which part of the error message is not clear? I don't know how else can we explain it to you. CTE can't have order by. – Eric Apr 12 '19 at 17:28
  • Thanks Sean Lange. I removed the ORDER BY clause and gave the right results. – Cunha Apr 12 '19 at 17:48
  • Thanks to Eric as well for quick response. – Cunha Apr 12 '19 at 17:50
  • @Cunha See my answer on **[SQL Server Query Error -ORDER BY clause is invalid in views](https://stackoverflow.com/questions/36697511/sql-server-query-error-order-by-clause-is-invalid-in-views/61702593#61702593)**. – Murat Yıldız May 09 '20 at 19:35

0 Answers0