SQL Server Query SELECT TOP 100 PERCENT allow VIEW ORDER BY
Posted: Tue Jan 18, 2005 10:34 am
BB
Regular
Joined: Jun 23, 2004
Posts: 340
I have been working on some views in Microsoft SQL server 2000.
Here is why you would use "SELECT TOP 100 PERCENT" in all the application views.
The answer is twofold
1. SQL query designer will put it there.
2. SQL Views do not allow an ORDER BY Clause unless the SELECT TOP 100 PERCENT is used.
SQL Server does not support the ORDER BY clause in a View, but you can get around that limitation by adding a TOP 100 PERCENT clause to the View. When you do, SQL Server interprets and correctly responds to an ORDER BY clause.
There is one problem with the TOP 100 PERCENT solution. As is, A View could drop a record from the results. When the last value has two or more records with the same value, the View includes only one. To avoid this problem, add the WITH TIES clause to the TOP statement in the form:
SELECT TOP 100 PERCENT WITH TIES.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
All logos and trademarks in this site are property of their respective owner. The comments are property of their posters, all the rest (c) 2006 by Group29 Productions.
You can syndicate Group29 Productions news with an RSS Feeder using the file backend.php