Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 2 of 2 total |
Is order by enough intelligent with Range? |
Fri, Mar 2 2012 4:36 AM | Permanent Link |
Laszlo Szabo | Hi!
I want to make some report that limited to a date, and I need to access the last element of the items. For example we have imports, and every import have articles with new prices. I want to get all LAST price of articles that lesser than the defined date. I want to ask that is order by of the query enough intelligent to get only last good rows by indexes without make result with all items, reverse it, and returns with first element? Tables: ImportMain (ID, Date, Kind) ImportItems (ID, MID, ArticleID, Price, Quantity) Article (ID, Name) select ii.*, im.Date, im.Kind from ImportItems ii left join ImportMain im on (im.ID = ii.MID) where im.Date < :Date and ii.ArticleID = :AID order by im.Date desc, im.Kind desc, im.ID desc range 1 to 1 May: It is enough intelligent then it can preuse indexes to get bigger elements (desc) and returns fast. It is making the query result with ALL prior rows, making a sort on this set, and returns with the first element. I asked this because I must optimize the request. Maybe it is better when I precalculate maximums: select ii.ArticleID, Max(im.Date)... group by ii.ArticleID And with these dates I get the import datas and get only the first needed row. Thanks for your help! |
Mon, Mar 5 2012 11:48 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Laszlo,
<< I want to ask that is order by of the query enough intelligent to get only last good rows by indexes without make result with all items, reverse it, and returns with first element? >> What you're asking is impossible. When using an ORDER BY on a result set that involves joins, the entire result set must be populated first before a range can be applied. It's the nature of such queries. There is one special case where this isn't true (LOJ with ORDER BY on left-side table columns), but it is fairly rare and such queries an often be coded as sensitive result set queries that use sub-queries in order to accomplish the same thing without the joins. << It is making the query result with ALL prior rows, making a sort on this set, and returns with the first element. >> Yes, that is what it will do. << I asked this because I must optimize the request. Maybe it is better when I precalculate maximums: >> That would probably work best for your needs, especially if you are primarily interested in the maximum date first before doing additional processing. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |