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!
   

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