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 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
> agentm(at)cmu(dot)edu
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>