Solved

ORDER BY in View with TOP PERCENT

Posted on 2011-09-26
9
420 Views
Last Modified: 2012-05-12
I want to control the (default) order of rows in a view by using an ORDER BY clause.  To do this I must use SELECT TOP in the view.

When I use SELECT TOP 100 then everything works as expected, but when I use SELECT TOP 100 PERCENT the query reverts to the clustered index order (or in the example below, the first column).  

Why is this the case?  It's just a matter of cosmetics to me really -- I'd rather use TOP 100 PERCENT rather than TOP 9223372036854775807, but I will use the latter if getting the former to work as expected is not easily done.

Thanks!
create table Catch22(rowid int identity(1,1), data varchar(80))
GO

insert into Catch22 select 'abc'
insert into Catch22 select 'yyz'
insert into Catch22 select 'pdq'
insert into Catch22 select 'xyz'
insert into Catch22 select 'cat'
GO

create view vCatch22 as
select top 100 rowid, data
from Catch22
order by data
GO

select * from vCatch22
GO

------ The above works as expected, but using top PERCENT below does not ... why?  -------

alter view vCatch22 as
select top 100 PERCENT rowid, data
from Catch22
order by data
GO

select * from vCatch22
GO

-- create unique clustered index x1 on Catch22(data,rowid)  -- this works, but is not applicable in my real scenario

drop view vCatch22
truncate table Catch22
drop table Catch22
--

Open in new window

0
Comment
Question by:knightEknight
  • 4
  • 3
  • 2
9 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 36600216
the select top ... order by no longer works from sql 2008 , that it ever worked has been described as an abheration by some of the MS development guys...

from now on you have to specify the order by clause  external to the view  as you always had to...(really)


... its to do with the fact that they now recognise top 100 percent is the whole table...


0
 
LVL 33

Author Comment

by:knightEknight
ID: 36600282
>> the select top ... order by no longer works from sql 2008

It seems to, as long as we use a constant and not PERCENT.  You are right though that it has never been guaranteed to work, but it is convenient in certain instances.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36600783
It does not work and is really not convenient. It just gives the appearance of being convenient. ORDER BY in view only parses if you have TOP, but if you then query the view you will find that it does not return the result set ordered. If it does then your data probably naturally orders that way anyway, in which case the ORDER BY is wasted overhead in the view definition anyway. You should ORDER BY in the final selection and create your view to get records "as-is".
0
 
LVL 33

Author Comment

by:knightEknight
ID: 36601013
The script above demonstrates that it does still work.  Convenience is in the eye of the beholder.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 33

Author Closing Comment

by:knightEknight
ID: 36601092
>> they now recognise top 100 percent is the whole table <<

Thanks!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36601343
knightEknight: not arguing with you. Works and guaranteed to work is the difference. I apologize if my choice of words came off harsh, but was supporting LowFatSpread in that it really did not *work* previously from standpoint of a guarantee. If you use ROW_NUMBER() for example, you will see some default ordering. There are things like that as in the example where you ORDER the content then LIMIT the list to TOP 100; therefore, it maintains this ordering when selecting from the view. GREAT! But what happens when it does not behave that way every time?

The attached is an extreme case, but try it out in AdventureWorks2008R2 and you will see that yes it is working but it really is because of the limit of rows after order. As LFS said, the 100% results in the "whole table" and so exposes that the ordering is really NOT being maintained.
CREATE VIEW vw_rndProductID
AS
SELECT TOP 5 ProductID
FROM Production.Product
ORDER BY NEWID()
;
GO

SELECT * FROM vw_rndProductID;

SELECT * FROM vw_rndProductID;

DROP VIEW vw_rndProductID;
GO

Open in new window

0
 
LVL 33

Author Comment

by:knightEknight
ID: 36601404
Thanks.  Understood that it is not a written guarantee, but in my case I want to select all rows every time from the view -- which is why I was trying the top 100 percent.  In reality I'm now using top (9223372036854775807), which does work every time regardless of the natural order or clustered index on the table.  But I do see that it wouldn't necessarily work if I was selecting top 5.  My question was why it works when using a constant (assuming the constant is > count(*) in the table) but not when using top 100 PERCENT.  LFS correctly points out that the latter is now synonymous with the whole table, whereas the former isn't necessarily so -- although in my case it is because my table will always have fewer than 9223372036854775807 rows in it. :)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36601419
*laughing* You are fine. What is funny is I saw your post as an another Expert comment. I did not see you ARE the question asker. Apparently, I need to go grab some coffee. :)
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36708898
just to conclude even with the top constant/number of rows

you are not guaranteed of any specific order of the returned rows if you do not specify an order by clause on the outermost
statement ...

consider tempdb usage, parallelism, ....

the dbms delivers what you ask it to in what it considers the most efficient manner... if you don't require it to provide the rows in  a specific order it is in the "lap of the gods" as to how they are returned.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now