Home » Listing Details
Top Websites
  1. Dynamics GP Help
    Over 6400 resources listed.
  2. Mark Polino's DynamicAccounting.net
    Over 5100 resources listed.
  3. Rose Business Solutions Blog New
    Over 2200 resources listed.
  4. Developing for Dynamics GP - By David Musgrave and the MS GP Dev Support Team
    Over 1100 resources listed.
  5. Mariano Gomez at The Dynamics GP Blogster
    Over 1000 resources listed.
  6. Microsoft Dynamics Partner Community Blog
    Over 900 resources listed.
  7. Christina Phillips, Steve Endow & Lorren Zemke at Dynamics GP Land
    Over 700 resources listed.
  8. Mohammad Daoud's Dynamics GP Blog
    Over 600 resources listed.
  9. Vaidy Mohan at Dynamics GP - Learn & Discuss
    Over 500 resources listed.
  10. Inside Microsoft Dynamics GP Official Blog
    Over 500 resources listed.
  11. eOne Business Solutions Blog
    Over 400 resources listed.
  12. About Dynamics, Development and Life
    Over 300 resources listed.
  13. Frank Hamelly at GP2theMax
    Over 300 resources listed.
  14. Dynamics CPM
    Over 300 resources listed.
  15. BKD Dynamics GP Insights Blog
    Over 200 resources listed.
  16. Leslie Vail at Dynamics Confessor Blogspot
    Over 200 resources listed.
  17. Victoria Yudin's Dynamics GP Website
    Over 200 resources listed.
    Victoria Yudin
  18. Janakiram M.P. at DynamicsBlogger
    Over 100 resources listed.
  19. VS Tools Forum
    Over 100 resources listed.
    Your Resource for Visual Studio Tools for Dynamics GP
  20. Inside Microsoft Dynamics GP Official Blog
    Over 100 resources listed.
  21. US Dynamics GP Field Team Blog
    Over 100 resources listed.
  22. Catherine Eibner MBS Developer Evangelist
    Over 100 resources listed.
  23. Sivakumar Venkataraman at Interesting Findings & Knowledge Sharing
    Over 100 resources listed.
  24. Dynamics Small Business
    Over 100 resources listed.
  25. Belinda, The GP CSI
    Over 100 resources listed.

ID:13795
Title:Using ORDER BY in a SQL Server 2005 or SQL Server 2008 View
URL:http://dynamicsgpland.blogspot.com/2010/06/using-order-by-in-sql-server-2005-or.html
Description:If you are a fan of SQL Server Views, you probably eventually noticed a change that occurred in SQL Server 2005:  Views no longer honored the ORDER BY clause.

With SQL Server 2000, ORDER BY clauses were 'sort of' honored, as you could use the TOP 100 PERCENT clause to pacify SQL 2000 and get your results in the requested order.

But with SQL Server 2005, the TOP 100 PERCENT clause no longer worked.  SQL purists would claim that SQL Views should not be ordered, and technically, I can understand that argument.

But I don't care about those technical or purist arguments.  I use SQL Server as a business tool, whose goal is to deliver data to business users and business owners in any form they want--not what is technically correct according to database engine developers.  If business owners want the data upside down and backwards, I need tools that help me get the job done.

Anyway, I'm currently working on a view that will provide a list of inventory items.  I won't be able to control how the client queries the view, so I can't control whether they add an ORDER BY clause on the view.  If they use Excel to query the view, I can pretty much guarantee that they won't be manually customizing the query, so I have no way of knowing how the data will be sorted once it gets into Excel.

Anyone who has done reporting for an accountant knows that arbitrary, random, and unpredictable are not three of their favorite words.  Therefore, I want to control how the data is sorted by the view, and know what my client is going to see when they query the view.

Looking into this issue after many years, I stumbled across this thread on the Microsoft SQL Server forum.  One of the participants posted a very interesting variant of the TOP 100 PERCENT clause that apparently tricks SQL Server 2005 into honoring the ORDER BY clause in a view.

The trick is to use a TOP # statement with a specific, but very large number.  He recommends just using the maximum integer value, which would be TOP 2147483647. 

Sure enough, this works like a charm, and I can now ensure that the results of my SQL Server 2005 view are ordered any way that I would like (well, I'm still working on the upside down request).

There is a potential question of whether this approach impacts performance.  For very large result sets or very complex queries, I suppose it could, but for Dynamics GP queries that are written properly, I very rarely have to worry about performance. 

I just tested this technique with SQL Server 2008, and it appears to still work.

Go forth and sort!
Category:SQL SCRIPTS
Link Owner:
Date Added:June 29, 2010 12:00:40 AM
Number Hits:13
RatingsAverage rating: (0 votes)
Reviews

No Reviews Yet.