Message-ID: <3DF785B3.7000609@klaster.net>
Date: Wed, 11 Dec 2002 19:36:35 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US;
	rv:1.2b) Gecko/20021016
MIME-Version: 1.0
To: "A.M." <agentm@cmu.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: Getting the latest unique items
References: <8A486724-0D34-11D7-BA9C-0030657192DA@cmu.edu>
In-Reply-To: <8A486724-0D34-11D7-BA9C-0030657192DA@cmu.edu>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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