Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 105F8475F3D for ; Thu, 12 Dec 2002 00:16:14 -0500 (EST) Received: from smtp5.andrew.cmu.edu (SMTP5.andrew.cmu.edu [128.2.10.85]) by postgresql.org (Postfix) with ESMTP id B0F2E476CF3 for ; Thu, 12 Dec 2002 00:15:33 -0500 (EST) Received: from cmu.edu (MCUBE.RES.cmu.edu [128.2.161.131]) (user=amcauley mech=PLAIN (0 bits)) by smtp5.andrew.cmu.edu (8.12.3.Beta2/8.12.3.Beta2) with ESMTP id gBC5FbbS021290 (version=TLSv1/SSLv3 cipher=DES-CBC3-SHA bits=168 verify=NOT) for ; Thu, 12 Dec 2002 00:15:37 -0500 Date: Thu, 12 Dec 2002 00:14:22 -0500 Subject: Re: Getting the latest unique items Content-Type: text/plain; charset=US-ASCII; format=flowed Mime-Version: 1.0 (Apple Message framework v548) From: "A.M." To: pgsql-sql@postgresql.org Content-Transfer-Encoding: 7bit In-Reply-To: <3DF785B3.7000609@klaster.net> Message-Id: <9352B184-0D90-11D7-BA9C-0030657192DA@cmu.edu> X-Mailer: Apple Mail (2.548) X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200212/142 X-Sequence-Number: 10730 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@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) > > > > > > > ><><><><><><><><>< AgentM agentm@cmu.edu