Out of order

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Dave62 » Out of order

35 posts, Page 2 of 4

««1 2 3 4 »»»

Out of order

Rate Topic

Display Mode

Topic Options

Author

Message

rfr.ferrari

Posted Friday, October 21, 2011 3:51 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 10:51 AM
Points: 1,219, Visits: 13,507

really it's a good question!!!!!

rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!

Post #1194227

codebyo

Posted Friday, October 21, 2011 4:11 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:30 AM
Points: 861, Visits: 1,436

Ha. Got it right. And it was a tricky question. Tongue

But a great one about a somewhat confusing subject (the ORDER BY, in that case, belongs only to the data filtered by the TOP clause and not the ordering of the whole query).

Thank you.

Best regards,

Best regards,

Andre Guerreiro Neto

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

Post #1194238

Mighty

Posted Friday, October 21, 2011 5:03 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:18 AM
Points: 2,720, Visits: 986

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.

Post #1194265

SQLRNNR

Posted Friday, October 21, 2011 5:36 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332

Thanks for the question.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1194283

Thomas Abraham

Posted Friday, October 21, 2011 5:36 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:02 AM
Points: 1,149, Visits: 1,451

Excellent! Thanks for the question. And thanks to others for the discussion of TOP 100 percent. I didn't realize the optimizer would throw that out. Of course, I've never used it, as I don't create many views, and never order them - leaving that for the user of the view. Still, it's good info to file away for later.

Please don't go. The drones need you. They look up to you.

Post #1194284

Britt Cluff

Posted Friday, October 21, 2011 6:07 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:17 PM
Points: 1,558, Visits: 247

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

http://brittcluff.blogspot.com/

Post #1194308

paul.knibbs

Posted Friday, October 21, 2011 6:30 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:43 AM
Points: 1,257, Visits: 4,255

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!

Post #1194333

codebyo

Posted Friday, October 21, 2011 6:31 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:30 AM
Points: 861, Visits: 1,436

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? BigGrin

Best regads,

Best regards,

Andre Guerreiro Neto

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

Post #1194334

L' Eomot Inversé

Posted Friday, October 21, 2011 6:34 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:48 PM
Points: 7,088, Visits: 7,143

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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #1194336

Sean Lange

Posted Friday, October 21, 2011 7:28 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:50 AM
Points: 8,595, Visits: 8,235

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1194371

« Prev Topic | Next Topic »

35 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions