You don't need to join tables, since x has both fields.
create table x (
symbol varchar,
prodid varchar,
date date
);
-- select mapping effective on given date
select symbol,prodid
from x
where date = (select max(date) from x where date <= ?);
--- Dan Bron <[EMAIL PROTECTED]> wrote:
> I need some help with SQL.
>
> I have a set of stock symbols and a set of internal product IDs which map
> 1-to-1 to those
> symbols. Sometimes the mapping changes (for example, when the symbol changes
> its meaning on the
> exchange).
>
> So I have a SQL table that tells me the symbol, the product ID, and the date.
> This table was
> initialized with all the symbols and their first product IDs, and a new row
> is added with
> symbol, (new) product_id, and date whenever a mapping changes.
>
> What I want is, for a given set of symbols, and a particular date, the
> product ID for that
> symbol as of that date.
>
> Obviously, the mapping for a particular symbol may changed 0 or more times on
> or before that
> date, so the table will have 1 or more symbol, product_id, date entries for
> each symbol.
>
> In J, a model of the function I want is:
>
>
> map_it =: verb define
> filter =. (symbol__x e. symbol__y) *. date__x <: some_date
>
> rows =. filter # prod_id__x ,. date__x
>
> prod_ids =. ({.@:/:/ @: |:/.~ {."1) rows
>
> symbol__y ,. prod_ids
> )
>
> NB. {.@:/: lots slower than {~ (i. >./)@:;
>
>
> where the arguments to the verbs are two objects (locales) representing the
> tables and, e.g.
> symbol__x is the symbol column from the left table.
>
> What I need is the SQL equivalent. I made the follow attempt, but it doesn't
> work:
>
>
> SELECT
> TOP 1
> y.symbol,
> x.prod_id,
>
> FROM
> x,
> y
>
> WHERE
> x.date <= @some_date
> AND
> x.symbol = y.symbol
>
> GROUP BY
> y.symbol
>
> ORDER BY
> x.date
> DESC
>
>
> the SQL engine complains that for each column I want in the output, I need to
> either include it
> in the GROUP BY clause, or use an aggregation function on it in the SELECT
> clause. Apparently
> TOP 1 doesn't count as an aggregation function.
>
> Suggestions?
>
> -Dan
>
>
>
>
>
>
> ----------------------------------------------------------------------
> For information about J forums see http://www.jsoftware.com/forums.htm
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
