Click here to Skip to main content
11,706,799 members (46,859 online)
Click here to Skip to main content

SQL Select Top 100 % forgets Order By Clause

, 19 Mar 2008 CPOL 20.4K 13
Rate this:
Please Sign up or sign in to vote.
SQL 2005+ changes behaviour of order by in relation to Select top 100%

Introduction

Sometimes, a developer can't trust his eyes, when things changes without warning. They just react in another way and no one knows why.

What are we talking about?

Using an Order By in a Select Query (in a view, inline function or something similiar) is a beginner level phrase. And it's nothing that challenges ... But in some cases, SQL Server just ignores the Order By Clause. Let's look at a common sql statement:

Select * from Customer Order By CompanyName, City

Now, this works normal. In some cases we add a Top 100% Clause

Select Top 100% from Customer Order By CompanyName, City

If we run this more or less same query, we see, that the rows now come unsorted...

Solution

After a few hours of research, we found in the SQL Server Books Online the following chapter:

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.

But for any reason, it does work with numeric Top

Select Top 10 from Customer Order By CompanyName, City

This works ... so our solution... we take the highest possible value for the Top Clause (what means a max(int) = 2147483647 and it works...

Select Top 2147483647 from Customer Order By CompanyName, City

will give us what we expect. Now you just have to change all your Top 100% and you are on the safe side for next SQL Server Releases from 2005 upwards.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Daniel Ch. Bloch (MCSD, MCAD, MCTS)
Software Developer (Senior) http://www.btv-data.ch
Switzerland Switzerland
On software development since 1992, going through the different steps from mainframe computing with VMS/Vax over VB4-6, Access and SQL server, Cold Fusions and ASP programming to my currently status as Chief Solution Developer for Smartclient and Client/Server development with vb.net and SQL Server. MCSD and some other stuff.


You may also be interested in...

Comments and Discussions

 
QuestionReg : Top 100 % Order by Pin
LAKSHMINARAYANAN E30-Mar-15 4:02
memberLAKSHMINARAYANAN E30-Mar-15 4:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150819.1 | Last Updated 19 Mar 2008
Article Copyright 2008 by Daniel Ch. Bloch (MCSD, MCAD, MCTS)
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid