CFF KB - Carrz-Fox-Fire Promotions Knowledge Base

CFF KB is all about 1 thing: The Sharing of Knowledge and the Power we gain from it.
  • Breadrumbs:
  • The ORDER BY clause is invalid in views

  • CFF Knowledge Base - Share With Facebook CFF Knowledge Base - Share on Twitter CFF Knowledge Base - Share on Reddit CFF Knowledge Base - Share on Digg It CFF Knowledge Base - Share on Stumble Upon It CFF Knowledge Base - Share on Delicious
    Share With Friends (Updated 6-8-2010)
  • Article ID:
    7269
  • Date Created
    Friday, March 1, 2024
  • This Article Has been Viewed
    189 times
  • Short Desc
    You may receive an error when creating a View with a SQL query. The ORDER BY clause is invalid in views.
  • Details
    You must use the TOP Clause when creating a SQL Query in SSMS View. When using the ORDER BY Clause, if not, you will receive the following error.

     
    Msg 1033, Level 15, State 1, Procedure Header_AlbumSales, Line 3 [Batch Start Line 0]
    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.
  • Recreate Issue
    You will receive this error when creating the new VIEW with the following code.

    Create View MyViewName_Here
    as
    Select Col2 from Table1 where Col1=1 order by Col1 Desc


    Even though this code will run in SSMS and will run on your website, it will not work in VIEW.
  • Resolve Issue
    In most cases, you will have to use a TOP Clause to add the Query to SSMS View when using a Group By.

    Create View MyViewName_Here
    as
    Select Top 1 Col2 from Table1 where Col1=1 order by Col1 Desc


    Having TOP 1, or whatever number you need, will allow the code to run in VIEW.