…. sorry for not updating for such a long time. 😛
just got a mail from a friend recently. probably this is some idea to share:
yo, Byeol. got a problem here. I have a table (FB) where I need to store every month’s stock of any available Product:
CREATE TABLE TSTOCK ( THEDATE DATE NOT NULL, PRODUCTID VARCHAR(30) NOT NULL, COST DOUBLE PRECISION DEFAULT 0 NOT NULL, LASTSTOCK DOUBLE PRECISION DEFAULT 0 NOT NULL ); ALTER TABLE TSTOCK ADD PRIMARY KEY (THEDATE, PRODUCTID);
the problem is … how to get only the last stocks of every available Product? the last date of each product might be different, like a certain product might be not updated this month but its last date was last month, while the others last dates are within this month. so basically, what I need is calling each Product’s last date row.
thanks in advance!
hmmm …. Jack, you meant this one?
select s.* from TSTOCK s where s.THEDATE = (select first 1 THEDATE from TSTOCK where (PRODUCTID = s.PRODUCTID) order by THEDATE desc)
anyone got a better idea??