From: Tomasz Myrta <jasiek@klaster.net>
To: A.M. <agentm@cmu.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: Getting the latest unique items
Date: Wed, 11 Dec 2002 19:36:35 +0100
Message-ID: <3DF785B3.7000609@klaster.net> (raw)
In-Reply-To: <8A486724-0D34-11D7-BA9C-0030657192DA@cmu.edu>
References: <8A486724-0D34-11D7-BA9C-0030657192DA@cmu.edu>
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)
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasiek@klaster.net, agentm@cmu.edu
Subject: Re: Getting the latest unique items
In-Reply-To: <3DF785B3.7000609@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox