On Nov 6, 2:54 am, xeni...@gmail.com wrote:
> Ted
>
> The statement
>
> AND B.price_date > A.price_date
> WHERE B.price_date IS NULL
>
> is contradictory. For B.price_date > A.price_date to return true,
> then B.price_date has to be NOT NULL (otherwise it returns null). On
> the next line you are asking B.price_date to be NULL. Seems like you
> are doing a lot of comparison work for no purpose.
>
> Also B.price_date is null will return unmatched rows in B or matched
> rows where B.price_date is null. Not sure if that is what you want?
> In fact I can't see a reason to have table B in there at all. What
> was your purpose for including it?
>
> X

This is what I was advised to use in the recent thread " Trouble
getting latest record for a given item." Articles in that thread give
URLs to additional sources, including MySQL documentation, that
describe using this method for getting the most recent value. The
essence of the rationale for it is that there will be exactly one
record in A that has no price greater than that in that record. We're
selecting records from a LEFT JOIN, so that join will have records in
A even for comparisons for which ALL values on the B side are NULL.

I agree there is a lot of comparison, but the question is, is there a
faster way to get the result. This is a worry since the table in
question has millions of records containing over 100 MB of data.

The first objective was to get the most recent price for a given
item. The next step is to get the last price for a given item for
each week for which there is data. The complication for this is that
while there is data for every business day, statutory holidays mean
that in some weeks the last business day of the week is a Thursday
rather than a Friday. It takes so long to get the last value for just
a couple dozen items, that using the same kind of query to get the
last record in each week, for series that go back 50 years, seems
impracticable: the application would just sit there apparently doing
nothing, for hours.

Thanks,

Ted