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
