Out of order

Question

Post reply

Out of order

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 1

Thanks for the question. What's the point in using TOP in a view?

http://brittcluff.blogspot.com/

paul.knibbs SSCoach Points: 15320 More actions · Answer 2

Britt Cluff (10/21/2011)
Thanks for the question. What's the point in using TOP in a view?

You might want the view to only return a certain number of rows. The question ought to be, why did Microsoft use the ORDER BY syntax to specify which rows you're returning when you do this, because it just causes people to think that views can be ordered!

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 3

Britt Cluff (10/21/2011)
Thanks for the question. What's the point in using TOP in a view?

I wonder that too since the result may be different in each SELECT of the view.

Maybe en example would be something like "VW_BestEmployeesEver" to show only the best 10 employees? 😀

Best regads,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Britt Cluff (10/21/2011)
Thanks for the question. What's the point in using TOP in a view?

OK, here is a real example that I had in one job.

In an interactive entertainment system, I want to know for example what are the 5 most frequently paid for PPV movies in my current catalogue; and the 5 least often paid for too. This helps plan future ordering of additional titles and retiring of existing titles. The view is basically a union of two select clauses each with a TOP and an ORDER BY clause. The view is actually quite complex, as (a) I'm only interested in recent payments, and (b) I'm only interested in movies that have been on the system for at least a month, (c) I may want separate information for "adult" movies, "family" movies, and "mainstream" movies, (d) I may want to treat recent releases and back catalogue and current catalogue differently, and (e) since the license fee demanded by the studios for a "blockbuster" is higher than that for an ordinary film I may want to split on that property too. Then I may want to split on license provider too, as different providers have different licensing systems; and I may want to split on territory since I have to either wait for official middle east cuts, make my own middle east cut (if the studio permits me to do that) or not supply the film in the middle east because of local censorship laws, and I have to take note of the difficulty of getting European/American films licensed in for example India (again because of local censorship laws) or even on an individual resort/hotel/residential complex/whatever because that, and not the viewer, is my customer and my contract with the customer requires me to have so many films in various categories (crime, adventure, scifi, whatever) at any time and to provide at least so many new films 10 times per year and so on. But those TOP and ORDER BY clauses are fundamental to the usefulness and function of the view(s). (Similar views for music, for TV channels, for radio stations, .... also need top and order by).

Tom

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Good question and with 40% incorrect answers with over 500 responses it seems a back to basics topic that lots of people don't know.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Dave62 SSCertifiable Points: 7459 More actions · Answer 6

Sean Lange (10/21/2011)
Good question and with 40% incorrect answers with over 500 responses it seems a back to basics topic that lots of people don't know.

Yes, that was quite a surprise to me. It got edited out but I included in my explanation that I expected 90 percent or higher correct answers.

I'm glad though that there hasn't been a lot of "trick question... I want my point back..." responses. :rolleyes:

SanDroid SSChampion Points: 10068 More actions · Answer 7

Mighty (10/21/2011)
Carlo Romagnano (10/21/2011)
CREATE VIEW vw_clients AS SELECT TOP 100 PERCENT Code, Name FROM Clients ORDER BY Code
I am curious to know if the VIEW above always returns records in sorted order.
As other people already said, in SQL 2005 and up the "TOP 100 PERCENT" will not be used. I had a legacy database with a lot of views that where using this. I had to change them to "TOP 99999999" in order to get them "working" again.

Did that work for you when there was less than 99999999 rows in the source data?

Dave62 SSCertifiable Points: 7459 More actions · Answer 8

SanDroid (10/21/2011)
Mighty (10/21/2011)
Carlo Romagnano (10/21/2011)
CREATE VIEW vw_clients AS SELECT TOP 100 PERCENT Code, Name FROM Clients ORDER BY Code
I am curious to know if the VIEW above always returns records in sorted order.
As other people already said, in SQL 2005 and up the "TOP 100 PERCENT" will not be used. I had a legacy database with a lot of views that where using this. I had to change them to "TOP 99999999" in order to get them "working" again.
Did that work for you when there was less than 99999999 rows in the source data?

It should work wether there are more or less records. You can even use a variable to limit the number of records returned or return them all.

USE AdventureWorks;

GO

DECLARE @TopRows AS int;

-- Set the variable for the number of rows to return based on the rows in the table

-- and give an extra 100

SET @TopRows = (Select COUNT(*) From HumanResources.Employee) + 100;

-- All rows are returned regardless of the extra 100

SELECT TOP(@TopRows) LoginID, Title, HireDate, @TopRows As "TopRows"

FROM HumanResources.Employee

ORDER BY HireDate;

-- Now set the variable to exclude the last 100 records in the table

SET @TopRows = (Select COUNT(*) From HumanResources.Employee) - 100;

SELECT TOP(@TopRows) LoginID, Title, HireDate, @TopRows As "TopRows"

FROM HumanResources.Employee

ORDER BY HireDate;

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 9

thanks for the question. - nice easy one to round out the week!

cheers

SanDroid SSChampion Points: 10068 More actions · Answer 10

Dave62 (10/21/2011)
....
I'm glad though that there hasn't been a lot of "trick question... I want my point back..." responses. :rolleyes:

That's most likely becuase there is a big difference between a trick question and a tricky subject.

You wrote a very good straight forward well worded question about a tricky subject.

Now, if you had made the answer True, and explained this was becuase ORDER BY is only used to define the column used to the Filter select Top statement you might have seen a lot of those responses. :w00t: 😎

Cliff Jones SSChampion Points: 10547 More actions · Answer 11

Dave62 (10/21/2011)
Sean Lange (10/21/2011)
Good question and with 40% incorrect answers with over 500 responses it seems a back to basics topic that lots of people don't know.
Yes, that was quite a surprise to me. It got edited out but I included in my explanation that I expected 90 percent or higher correct answers.
I'm glad though that there hasn't been a lot of "trick question... I want my point back..." responses. :rolleyes:

Drat, I forgot all about the Top thingy! After 20 years of doing this I guess it is back to the basics for me. I don't deserve my point back (good question) but I do want to take a mulligan.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 12

Daniel Bowlin

SSC-Dedicated

Points: 34566

October 21, 2011 at 9:41 am

#1398082

Good one, thanks.

Ken SSCoach Points: 17191 More actions · Answer 13

Ken

SSCoach

Points: 17191

October 21, 2011 at 9:50 am

#1398087

Nice question. Thanks!

SanDroid SSChampion Points: 10068 More actions · Answer 14

Dave62 (10/21/2011)

SanDroid (10/21/2011)
......
It should work wether there are more or less records. You can even use a variable to limit the number of records returned or return them all.
......

I should have been more specific in my question.

Does using a "TOP 99999999" statement with an "order by" in a VIEW against an actual table with less than 99.9 Million rows keep the query optimizer from ignoring the Order by statement since nothing will be filtered?

I know in a basic select with a variable it will not, but Views are different. 😎

CoolCodeShare SSCrazy Points: 2584 More actions · Answer 15

CoolCodeShare

SSCrazy

Points: 2584

October 21, 2011 at 12:03 pm

#1398165

Nice Question.