Re: Getting the latest unique items

When I try to run the following query:

select distinct on(student_gradedmaterial.id) student_gradedmaterial.id
from coursesection_student,student_gradedmaterial WHERE
gradedmaterialid=1 AND
coursesection_student.studentid=student_gradedmaterial.studentid AND
coursesectionid=1 and score is not null order by submittime desc;

I get the following error:

ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY
expressions

Should I use GROUP BY somehow?
SELECT DISTINCT ON (student_gradedmaterial.id)
student_gradedmaterial.id from
coursesection_student,student_gradedmaterial where gradedmaterialid=1
and coursesection_student.studentid=student_gradedmaterial.studentid
and coursectionid=1 and score is not null having max(big subselect of
max times);

?

The relevant schema follows:
CREATE TABLE coursesection_student (
coursesectionid INT8 REFERENCES coursesection,
studentid INT8 REFERENCES student,
status INT4 DEFAULT 0 NOT NULL, --2 switched, 1 dropped, 0
enrolled
touch BOOL DEFAULT 'true',
UNIQUE(coursesectionid,studentid)
);
CREATE TABLE coursesection_ca(
coursesectionid INT8 REFERENCES coursesection,
caid INT8 REFERENCES ca
);
CREATE TABLE gradedmaterial (
id SERIAL PRIMARY KEY,
name TEXT,
visible BOOLEAN DEFAULT 'f',
openforsubmission BOOLEAN DEFAULT 'f',
description TEXT,
webpage TEXT,
predefcomments TEXT,
weight INT4,
restrictedfiletypes TEXT,
duetime TIMESTAMP
);

CREATE TABLE coursesection_gradedmaterial(
gradedmaterialid INT8 REFERENCES gradedmaterial,
coursesectionid INT8 REFERENCES coursesection
);

CREATE TABLE student_gradedmaterial(
id SERIAL,
studentid INT8 REFERENCES student,
gradedmaterialid INT8 REFERENCES gradedmaterial,
caid INT8 REFERENCES ca,
score INT4,
comments TEXT,
submittime TIMESTAMP,
gradedtime TIMESTAMP,
file OID,
emailtostudent BOOLEAN DEFAULT 'f',
suffix VARCHAR(6) DEFAULT '.zip'
);
On Wednesday, December 11, 2002, at 01:36 PM, Tomasz Myrta wrote:

> 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)
> >
>
>
>
>
>
><><><><><><><><><
AgentM
agentm(at)cmu(dot)edu