I'm not sure if I understood your problem, but did you try with "distinct on"? select distinct on (id) from ... order by submittime desc
Regards, Tomasz Myrta A.M. wrote: > I have a table as follows: > CREATE TABLE student_gradedmaterial( > id SERIAL, > studentid INT8 REFERENCES student, > gradedmaterialid INT8 REFERENCES gradedmaterial, > caid INT8 REFERENCES ca, > ... > submittime TIMESTAMP, > gradedtime TIMESTAMP, > score INT4 > ); > > Every time a student submits a homework, one new entry in the table is > created. I know how to grab the latest version based on the submittime > but naturally, I'd like to be able to count how many homeworks are > graded and ungraded (ungraded means score is NULL). This smells of a > subselect: > > graded (grab row count): > SELECT UNIQUE id FROM student_gradedmaterial WHERE EXISTS (SELECT the > latest unique submissions); > or: > SELECT COUNT(score) FROM student_gradedmaterial WHERE gradedmaterialid=X > AND submittime = MAX(SELECT submittime FROM student_gradedmaterial WHERE > gradedmaterialid=X); > > (Sub-selects just make my head explode.) Any hints for me? Thanks. > ><><><><><><><><>< > AgentM > [EMAIL PROTECTED] > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
