The query below uses a join (self-join). You can use
SQL99 RANK() functions if you want to avoid the
self-join.
For e.g., if you have Oracle 9i or above:
create table x(symbol varchar2(30),
prodid varchar2(30),
mydate date);
select symbol, prodid
from (select symbol, prodid,
RANK() OVER (PARTITION BY symbol
ORDER BY mydate DESC)
as myrank
from x
where mydate <= ...)
where myrank = 1;
[Jprogramming] SQL equivalent of J function
Oleg Kobchenko olegykj at yahoo.com wrote:
> 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 <jf at dbron.net> 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
__________________________________________________
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
