get rows of dynamic latest date(s)

…. 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!

- Jack

—

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??