Francisco Tapia
fhtapia at gmail.com
Fri May 20 16:43:25 CDT 2011
John, Please review my notation, of TOP 99.99999 percent (I think you can go out 9 digits after the decimal, there is a bug that for some reason did not get addressed until after sql server 2008 came out, and now you need to apply hotfixes to get it to work right (or just switch from top 100 percent to top 99.99999 percent http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b926292&sd=rss&spid=2855 add that to your view and you should see the results get re-organize appropriately w/o having to apply the hotfix. i know that for a while ms was taking the position that the order by clause in a view was non-ansi92 standards compliant, but then... how much of their tsql is? :) all vendors make concessions, but I think they had some bug within the engine during the optimization process. in Access if you create the link to your view as a LINKED query you can optionally also choose the query to be as Select * from viewMyView Order by MyCriteria = criteria and that will also provide all the heavy lifting on the sql engine side. hth... -Francisco http://bit.ly/sqlthis | Tsql and More... http://db.tt/JeXURAx | Drop Box, Storage in the Cloud (free) On Fri, May 20, 2011 at 10:26 AM, jwcolby <jwcolby at colbyconsulting.com>wrote: > Francisco, > > The sql from the view. > > SELECT TOP (100) PERCENT INM_ID, INM_LName + ', ' + INM_FName AS Name > FROM dbo.tblInmate > WHERE (INM_Active <> 0) > ORDER BY Name > > This specific view is sorting ascending on the name field (calculated > obviously). > > It does return sorted if I open it in design view and then bang the query. > > 32 BROWN, ANTHONY > 35 CHISGAR, DENNIS > 29 COCHRAN, ROBERT > 33 COLLINS, WILLIE > 36 EDMISTEN, FRED > 37 EDWARDS, CASEY > 6 ERVIN, DAVID > > but if I open a query and do select * from QryXYZ > > INM_ID Name > 4 HUSH, KEITH > 5 HODSDEN, MARK > 6 ERVIN, DAVID > 10 PRATT, BAXTER > 11 POWELL, BOBBY > 15 TURNER, CHARLES > 17 HARDISON, DAVID > 19 JONES, JEFFREY > > It appears that the query is getting the data sorted by the PKID (which has > a clustered index on it). Back in access I get the same "ordered by PKID" > order. > > > John W. Colby > www.ColbyConsulting.com > > On 5/20/2011 1:07 PM, Francisco Tapia wrote: > >> So something like: >> >> Create View vwSomeView AS >> Select TOP 99.9999 percent Field1, Field2, Field3 >> >>> From tblSomeTable >>> >> Order by Field3 >> >> does not sort field3? what are your results when you just select * from >> vwSomeView ? are the results sorted in your results display in management >> studio? >> >> >> -Francisco >> <http://bit.ly/sqlthis> >> >> >> >> >> On Fri, May 20, 2011 at 10:02 AM, jwcolby<jwcolby at colbyconsulting.com >> >wrote: >> >> One of the things I am trying to do is use SQL Server to speed up my >>> applications. The theory is that I can hand off the heavy lifting to SQL >>> Server and just get back result sets. Of course this works in terms of >>> joins and filters in a view, but even though I specify a sort in a view, >>> when the result set hits the other end (Access in my case) it is >>> unsorted. >>> >>> Views have the ability to do sorts, so why is the data returned by a view >>> into a third party app, or even into another view in SQL Server unsorted? >>> Is there a way to tell sql server to return sorted data? >>> >>> >>> >>> _______________________________________________ >> dba-SQLServer mailing list >> dba-SQLServer at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >> http://www.databaseadvisors.com >> >> >> _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >