Douglas Fentiman wrote: >> SELECT * FROM thetable a >> WHERE ROUND(theid,-2) <> theid >> OR (ROUND(theid,-2) = theid AND (SELECT count(*) FROM thetable b WHERE >> b.theid > a.theid AND b.theid <= a.theid + 99) = 0) > > Thanks Jim! > > Initial testing shows it works great! Interesting use of round(). > > Takes about 1.0 second to return 904 records from 955 total. > > Any ideas on how I could make it quicker? > > Doug >
A clustered index on the id column should help, if you don't already have one. I'm surprised that it takes a second to return the results with that small of a dataset, but I'm betting that to get much better performance, you will have to have some indexable indication of what is a parent and what is not (perhaps a parent field that you store a TRUNCATE(theid,-2) in ...in fact, try a TRUNCATE in the above instead of the round...that may be slightly more efficient). But I can't think of anything where you aren't going to have to touch every row with some function to figure out the data you need, and that is costly. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2765 Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
