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).