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])
