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
entryin the table is> created. I know how to grab the latest version based on the submittime> but naturally, I'd like
tobe 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
uniquesubmissions);> or:> SELECT COUNT(score) FROM student_gradedmaterial WHERE gradedmaterialid=X> AND submittime =
MAX(SELECTsubmittime FROM student_gradedmaterial WHERE> gradedmaterialid=X);>> (Sub-selects just make my head explode.)
Anyhints for me? Thanks.> ><><><><><><><><><> AgentM> agentm@cmu.edu>>>> ---------------------------(end of
broadcast)--------------------------->TIP 2: you can get off all lists at once with the unregister command> (send
"unregisterYourEmailAddressHere" to majordomo@postgresql.org)>
