Ooh … Begitu! I see, I see …

Icon

Setelah kelayapan di internet, cari-cari info dari sana-sini dan bongkar pasang tips (terutama programming), akhirnya baru sadar … ternyata begitu caranya … [all about tricks and tips I have found during my work as programmer.]

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

Advertisements

Filed under: Firebird, , , ,