Mark Breen
marklbreen at gmail.com
Mon May 23 03:21:32 CDT 2011
Hello All, I too have seen SQL Server / Access not return the sorted records. I am intrigued by Francisco's suggestion to try the 99.99...... options, but I am in the habit now of never ordering within a view and always ordering when i select data from a view. In the cases where I can use an sproc, then I do not use a view at all, just include the select that would go in the view in the sproc instead. Then I can safely sort in the sproc and no worries about the client end. John, may I tell you about a trick that I sometimes do, and please exclude me if you are already doing this. I create a Past Through query in Access, which as you know, ignores jet and sends the query straight to the source db (SQL Server in this case). I then programatically change the 'SQL' of the qdef based on what I want to do. Sometimes, I just call the pass-through query qpstTemplate. The template bit being the connection string. Sometimes I have two, qpstTemplate_ReturnsRecords and qpstTemplate_NoRecords. The 'returns no records' flag is set in the second one and I can use that for action sprocs. I then set the sql to be *usp_GetCustomers*, then later set it again to * usp_GetOrdersByOrderDate* As a result, I often do not need to sort within Access. As you instinctively know, asking Jet to do this work is not the right course. Do you want to let SQL server do the heavy lifting for you. Any help? thanks Mark On 21 May 2011 10:33, Asger Blond <ab-mi at post3.tele.dk> wrote: > Thanks for the explanation, Stuart. Still I've never seen the result you > indicate. The TOP 100 PERCENT is a special case where the query engine will > ignore the ordering specified since you are actually requesting all rows. > But if you force the engine to make a selection using TOP 99.999999999 > PERCENT then it makes no sense to me why the engine should not return the > rows in the order it's being forced to use for the selection. And certainly > if you use TOP 5 it would be quite inefficient for the engine not to return > the ordered rows. So: when the engine is forced to use a condition (TOP 5 or > TOP 99.999999999) I've never seen a situation where the rows are not > returned in the order specified by the query - whether this is embedded in a > view or just run as a plain query. > Asger > > -----Oprindelig meddelelse----- > Fra: dba-sqlserver-bounces at databaseadvisors.com [mailto: > dba-sqlserver-bounces at databaseadvisors.com] På vegne af Stuart McLachlan > Sendt: 21. maj 2011 06:29 > Til: Discussion concerning MS SQL Server > Emne: Re: [dba-SQLServer] Views don't sort > > It means that if you specify "SELECT TOP 5 FROM ALPHABET ORDER BY LETTER", > you > will always get A,B,C,D and E, but they may not necessarily be in that > order. They may be > returned as "E,D,C,B,A", "D,B,A,E,C" etc. > > Although it has worked up til now, it is just like any other hack that uses > "undocumented > features". - after the next patch, hotfix or service pack, you may find > that it no longer works > that way - the same records could be returned in a completely different > sort order, possibly by > PK or by the order in which they are physically stored on disk. > > -- > Stuart > > On 21 May 2011 at 0:09, Asger Blond wrote: > > > Well, and this quote from BOL just doesn't make any sense to me. The > > TOP and ORDER BY clause is used to "determine the rows returned", but > > it "does not guarantee ordered results" - WTF does this mean? I use > > the construct specified by Francisco, and have never seen problems. > > Asger > > > > -----Oprindelig meddelelse----- > > Fra: dba-sqlserver-bounces at databaseadvisors.com > > [mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af > > Stuart McLachlan Sendt: 20. maj 2011 22:48 Til: Discussion concerning > > MS SQL Server Emne: Re: [dba-SQLServer] Views don't sort > > > > Be careful with that. It is not guaranteed to work! > > > > See http://msdn.microsoft.com/en-us/library/ms188385.aspx > > <quote> > > The ORDER BY clause is not valid in views, inline functions, derived > > tables, and subqueries, unless TOP is also specified. ... When ORDER > > BY is used in the definition of a view, inline function, derived > > table, or subquery, the clause is used only to determine the rows > > returned by the TOP clause. The ORDER BY clause does not guarantee > > ordered results when these constructs are queried, unless ORDER BY is > > also specified in the query itself </quote> > > > > The only way to be sure is to use "Select * from vwMyView Order by > > colMyCol" > > > > -- > > Stuart > > > > On 20 May 2011 at 13:31, jwcolby wrote: > > > > > Francisco, > > > > > > I apparently ignored your top 99.999% part. > > > > > > When I went back in to my view and selected top 1 million (very big) > > > it did in fact return a sorted data set. > > > > > > Thanks! > > > > > > 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 > > > > > > > > > > > > > > _______________________________________________ > > 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 > > > > > > > _______________________________________________ > 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 > >