Getting the latest unique items

From: "A(dot)M(dot)" <agentm(at)cmu(dot)edu>
To: pgsql-sql(at)postgresql(dot)org
Subject: Getting the latest unique items
Date: 2002-12-11 18:15:34
Message-ID: 8A486724-0D34-11D7-BA9C-0030657192DA@cmu.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2002-12-11 18:20:34 Re: error in copy table from file
Previous Message mzmaxmail 2002-12-11 17:40:48 error in copy table from file