On 1/2/08, Jonathon Stierman <[EMAIL PROTECTED]> wrote: > You can see in the "All Samples" section that records 20-30 should be: [147, > 148, 149, 150, 63, 36, 40, 382, 383, 384]. > But the Total Wrapper query returns: [146, 147, 148, 149, 150, 63, 382, 383, > 384, 440]. > What happened to record 40? It's row #27 in the All Samples query, but it's > included in the exclude Nested Subquery that should only be pulling the top > 20 results. How does that happen? > Since the sort order you are using (dateAdded) is not unique, it is probably using some other factor (like a clustered index or record insertion order) along with dateAdded to sort the records returned. Use something like this...
SELECT TOP 10 sampleID, dateAdded FROM Samples a WHERE 0=0 AND a.sampleID NOT IN ( SELECT TOP 20 b.sampleID FROM Samples b ORDER BY b.dateAdded ASC,b.currentRow ) ORDER BY a.dateAdded ASC,a.currentRow; However, I'm guessing that the above will not scale well with a very large dataset, so something like the SP sent earlier may be a better solution. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295747 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
