pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting the latest unique items
5+ messages / 3 participants
[nested] [flat]
* Getting the latest unique items
@ 2002-12-11 18:15 A.M. <agentm@cmu.edu>
2002-12-11 18:36 ` Re: Getting the latest unique items Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 5+ messages in thread
From: A.M. @ 2002-12-11 18:15 UTC (permalink / raw)
To: 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@cmu.edu
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting the latest unique items
2002-12-11 18:15 Getting the latest unique items A.M. <agentm@cmu.edu>
@ 2002-12-11 18:36 ` Tomasz Myrta <jasiek@klaster.net>
2002-12-12 05:14 ` Re: Getting the latest unique items A.M. <agentm@cmu.edu>
0 siblings, 1 reply; 5+ messages in thread
From: Tomasz Myrta @ 2002-12-11 18:36 UTC (permalink / raw)
To: A.M. <agentm@cmu.edu>; +Cc: pgsql-sql
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)
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting the latest unique items
2002-12-11 18:15 Getting the latest unique items A.M. <agentm@cmu.edu>
2002-12-11 18:36 ` Re: Getting the latest unique items Tomasz Myrta <jasiek@klaster.net>
@ 2002-12-12 05:14 ` A.M. <agentm@cmu.edu>
2002-12-12 07:13 ` Re: Getting the latest unique items Tom Lane <tgl@sss.pgh.pa.us>
2002-12-12 08:34 ` Re: Getting the latest unique items Tomasz Myrta <jasiek@klaster.net>
0 siblings, 2 replies; 5+ messages in thread
From: A.M. @ 2002-12-12 05:14 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting the latest unique items
2002-12-11 18:15 Getting the latest unique items A.M. <agentm@cmu.edu>
2002-12-11 18:36 ` Re: Getting the latest unique items Tomasz Myrta <jasiek@klaster.net>
2002-12-12 05:14 ` Re: Getting the latest unique items A.M. <agentm@cmu.edu>
@ 2002-12-12 07:13 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2002-12-12 07:13 UTC (permalink / raw)
To: A.M. <agentm@cmu.edu>; +Cc: pgsql-sql
"A.M." <agentm@cmu.edu> writes:
> When I try to run the following query:
> select distinct on(student_gradedmaterial.id) ...
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> ... order by submittime desc;
^^^^^^^^^^^^^^^^^^^
> I get the following error:
> ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY
> expressions
Indeed. You might benefit from reading the DISTINCT ON usage example
given in the SELECT reference page,
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/sql-select.html
(see under "Description"). Basically, DISTINCT ON is *only* useful when
designed hand-in-hand with an ORDER BY ...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Getting the latest unique items
2002-12-11 18:15 Getting the latest unique items A.M. <agentm@cmu.edu>
2002-12-11 18:36 ` Re: Getting the latest unique items Tomasz Myrta <jasiek@klaster.net>
2002-12-12 05:14 ` Re: Getting the latest unique items A.M. <agentm@cmu.edu>
@ 2002-12-12 08:34 ` Tomasz Myrta <jasiek@klaster.net>
1 sibling, 0 replies; 5+ messages in thread
From: Tomasz Myrta @ 2002-12-12 08:34 UTC (permalink / raw)
To: A.M. <agentm@cmu.edu>; +Cc: pgsql-sql
A.M. wrote:
> 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
No
You have to change your sort order (as in error above). It should be:
order by student_gradematerial.id asc, submittime desc;
Tomasz Myrta
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-12-12 08:34 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-11 18:15 Getting the latest unique items A.M. <agentm@cmu.edu>
2002-12-11 18:36 ` Tomasz Myrta <jasiek@klaster.net>
2002-12-12 05:14 ` A.M. <agentm@cmu.edu>
2002-12-12 07:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-12-12 08:34 ` Tomasz Myrta <jasiek@klaster.net>
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