Here's sqlite test.
"in (?,?)" should have appropriate number of "?"'s.
NB. =========================================================
require 'data/sqlite files'
DB=: jpath '~temp/prod_date.sqlite'
PR=: ;:'P1 P1 P2 P2 P3 P3 P3'
SM=: ;:'S11 S12 S21 S22 S31 S32 S33'
DT=: 11 12 11 30 12 22 31
sqlProdDate=: 0 : 0
select x.PR,x.SM,x.DT
from x, (select PR,max(DT) MD from x
where DT <= ? and PR in (?,?)
group by PR) y
where x.PR=y.PR and x.DT=y.MD
)
Note'sqlite test'
ferase DB
]db=: 'psqlite'conew~ DB
exec__db 'create table x (PR varchar(20),SM varchar(20),DT date);'
(PR,.SM,.<"0 DT)apply__db 'insert into x values (?, ?, ?);'
colquery__db 'select * from x;'
colquery__db 'select * from x where PR in ("P1","P2");'
(;:'P1 P3') colquery__db 'select * from x where PR in (?);'
((;:'P1 P3'),<12) colquery__db 'select * from x where PR in (?,?) and DT<=?;'
(25;;:'P1 P2') colquery__db sqlProdDate
(32;;:'P1 P3') colquery__db sqlProdDate
destroy__db''
)
NB. =========================================================
ferase DB
1
]db=: 'psqlite'conew~ DB
+--+
|26|
+--+
exec__db 'create table x (PR varchar(20),SM varchar(20),DT date);'
0 0
(PR,.SM,.<"0 DT)apply__db 'insert into x values (?, ?, ?);'
1 2 3 4 5 6 7
colquery__db 'select * from x;'
+--+---+--+
|PR|SM |DT|
+--+---+--+
|P1|S11|11|
|P1|S12|12|
|P2|S21|11|
|P2|S22|30|
|P3|S31|12|
|P3|S32|22|
|P3|S33|31|
+--+---+--+
((;:'P1 P3'),<12) colquery__db 'select * from x where PR in (?,?) and DT<=?;'
+--+---+--+
|PR|SM |DT|
+--+---+--+
|P1|S11|11|
|P1|S12|12|
|P3|S31|12|
+--+---+--+
(25;;:'P1 P2') colquery__db sqlProdDate
+--+---+--+
|PR|SM |DT|
+--+---+--+
|P1|S12|12|
|P2|S21|11|
+--+---+--+
(32;;:'P1 P3') colquery__db sqlProdDate
+--+---+--+
|PR|SM |DT|
+--+---+--+
|P1|S12|12|
|P3|S33|31|
+--+---+--+
destroy__db''
1
--- Oleg Kobchenko <[EMAIL PROTECTED]> wrote:
> I am not sure about the rank SQL, but my SQL was
> wrong. It is close to SQL for "One product" but still
> would require a product parameter.
>
> Here's hopefully correct examples. The key here is
> designating good test data sets.
>
> NB. =========================================================
> PR=: ;:'P1 P1 P2 P2 P3 P3 P3'
> SM=: ;:'S11 S12 S21 S22 S31 S32 S33'
> DT=: 11 12 11 30 12 22 31
>
> Note 'DDL'
> create x (
> PR varchar(32),
> SM varchar(32),
> DT date
> );
> )
>
> NB. =========================================================
> Note 'One product'
>
> select PR,SM,DT
> from x
> where DT = (select max(DT) from x
> where DT <= @d and PR = @p)
>
> p=. <'P3'
> d=. 25
> i=. DT i. >./ ((PR=p) *. (DT<:d)) # DT
> (>i{PR) ; (>i{SM) ; (,.i{DT)
> +--+---+--+
> |P3|S32|22|
> +--+---+--+
> )
>
> NB. =========================================================
> Note 'List of products'
>
> select x.PR,x.SM,x.DT
> from x, (select PR,max(DT) MD from x
> where DT <= @d and PR in @p
> group by PR) y
> where x.PR=y.PR and x.DT=y.MD
>
> p=. ;:'P1 P2'
> d=. 25
> ]i=. (PR e. p) *. (DT <: d)
> 1 1 1 0 0 0 0
> (>~.i#PR) ; (,.(i#PR) >.//. i#DT)
> +--+--+
> |P1|12|
> |P2|11|
> +--+--+
> (>~.i#PR) ; (,.(i#PR) >.//. i#DT) ; (":@>(i#PR) </. i#i.#DT)
> +--+--+---+
> |P1|12|0 1|
> |P2|11|2 |
> +--+--+---+
> ]j=. (i#PR) ({~ (i#DT) i. [: >./ {&(i#DT))/. i#i.#DT
> 1 2
> (>j{PR) ; (>j{SM) ; (,.j{DT)
> +--+---+--+
> |P1|S12|12|
> |P2|S21|11|
> +--+---+--+
> )
> NB. =========================================================
__________________________________________________
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
