Re: Getting the latest unique items

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