Order by clause in View doesn't order.

Order by clause in View doesn't order.

• pushart

  Hi sg2000,

  You can use
  TOP (99) PERCENT
  instead of
  TOP (100) PERCENT

  The results for the query "Select top 99 percent..." from a table, which has X row(s), is X row(s), even if the table has only one row.

  Then, you will be able to use the "order by" clause whitout need to change anything in your application and without giving less rows as result.
  

  
  
  

• Mercury

  We have lot of views in SQL server 2000. It will be big pain to convert to SQL 2005. i.e. we need to change views to remove 'order by' and then need to identify the calls to the view and add order by instead. This is going to be big problem in conversion from SQL Server 200 to SQL Server 2005 going forward. Do you see any easy way to acomplish this

  
  
  

• Macktr

  Because of the last bit:

  The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified

  And yes, it is still a bit retarded, but the whole New View screen is pretty unpleasant if you ask me. And yes again, it is ironic that a tool that is made to make things easier for newer users often makes it less easy. Go figure :)

  
  
  

• Tony Robyn

  OK, this is absolutely retarded. 

   

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm

  This says :

  Specifies the sort order used on columns returned in a SELECT statement. The ORDER BY clause is not valid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

   

  If it says "The ORDER BY clause is not valid in views" then why the heck is there a "sort by" column in the New View Screen    

   

  
  
  

• Dominik Vogel

  Hello,

  I dont know if its allowed here, but there is a tool that allows you to find references to SQL Server objects even in sourcecode. Take a closer loot at the apexsql suite of tools. They will help you to identify where calls to those views are made. I am not sure which product was able to do this, but i think it was ApexSQL Clean ( http://www.apexsql.com/sql_tools_clean.asp )

  If tossing this add in here was not allowed post so and ill remove the link.

   

  (Edit: Free No-Fuss trial Version is available for 30 days)

  
  
  

• Jo0815

  Well, that's what I am doing for now, inserting order by in stored procedure. It's very confusing and order by shouldn't be valid in views.
  

• Highlander70

  There are no options unfortunately. Specifying ORDER BY in the outermost SELECT statement in your query is the only way to ensure that the rows are returned in a particular order to the client.
  
  

• Mark Gialo

  It is allowed as long as it is on topic and not just an advertisement. Giving us information about legit tools that help with SQL Server development/managment is great.

  
  
  

• flamingwoodchuck

   From Books Online 2005:
  
  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/bb394abe-cae6-4905-b5c6-8daaded77742.htm
  When ORDER BY is used in the definition of a view, inline
  function, derived table, or subquery, the clause is used
  only to determine the rows returned by the TOP clause.
  The ORDER BY clause does not guarantee ordered results
  when these constructs are queried, unless ORDER BY is
  also specified in the query itself.
  
  If you want to see the contents of the view in a particular
  order, you must specify ORDER BY when you query the view.
  The ORDER BY in the view definition here does nothing at all.
  
  It's unfortunate that TOP (100) PERCENT is allowed, let alone
  added by the view designer, and my suggestion is never use
  TOP (100) PERCENT, because it is meaningless and leads you
  to think you can create an "ordered view", which you cannot.
  
  Steve Kass
  Drew University
  
  
  sg2000@discussions.microsoft.com wrote:
  > I have created view by jaoining two table and have order by clause. 
  > 
  > The sql generated is as follows
  > 
  > SELECT   TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
  > FROM     dbo.UWYearDetail INNER JOIN
  >            dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId
  > = dbo.UWYearGroup.UWYearGroupId
  > ORDER BY dbo.UWYearDetail.PlanVersionId,
  > dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear,
  > dbo.UWYearGroup.MandDFlag, 
  >            dbo.UWYearGroup.EarningsMethod,
  > dbo.UWYearGroup.EffectiveMonth
  > 
  > 
  > 
  > If I run sql the results are displayed in proper order but the view only
  > order by first item in order by clause.
  > 
  > Has somebody experience same thing  How to fix this issue 
  > 
  > Thanks,
  > 
  > 
  > 
  > 
  

  
  

• senthilrd

  No problemo. I agree with you wholeheartedly that it should be better than it is, either way :)
  
  

• Hoop

  not trying to be harsh...i had a little wine in me at the time and things just came out. (this is my drunk apology haha) ...
  
  

• mgoetzke

  Yes the tools will be helpful but what if my application is calling view from dlls or externally. I don't think any tool will hepl here. I have application built with rapid application builder called Ironspeed and it generates code which is using view.

  What are my options other than manully going in my application to fix ( which seems painful), to keep the things working as it were before in SQL 2000

  
  
  

• MarkHenneman

  This is a common complaint, but if order by wasn't allowed, then TOP (anything other than 100%) would not be of much use. That is why you can't put an order by clause in the view definition without the TOP clause.

  The bottom line is, like Steve said, there is no guarantee that tables have any order, including views with Order By clauses, Tables with clustered indexes, etc. This allows the optimizer to work with the hardware in the very fastest manner if you don't explicitly ask for rows in a given order.

  It's kind of annoying, but it makes sense and is not a real problem once you get used to it (and it took me a long time when I first realized it too :)

  
  
  

• T-off

  This still doesn't guarantee that rows will be returned in the same order to client. The whole point is not about use of TOP 100 PERCENT with ORDER BY. Any ORDER BY clause in derived table or view for example only applies within that scope. To present rows in a particular order to client, you HAVE to include ORDER BY clause in the outermost SELECT statement. This is the only way the query will produce expected results always. See blog post below also for ordering guarantees in SQL Server:

   

  http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

  
  
  
• Order by clause in View doesn't order.