Non-aggregate values attached to aggregates?

From: Benjamin Smith <lists(at)benjamindsmith(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Non-aggregate values attached to aggregates?
Date: 2004-12-16 21:38:19
Message-ID: 200412161338.19746.lists@benjamindsmith.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a list of students, and a list of enrollment records, and I'm trying to
get a list of students and their most recent enrollment/disenrollment dates.

create table students (id serial primary key, name varchar);
create table enrollments (
students_id integer not null references students(id),
start integer not null,
finish integer not null default 0);
insert into students (name) VALUES ('johnny');
insert into enrollments (students_id, start, finish) VALUES
(1, 20030901, 20040530);
insert into enrollments (students_id, start, finish) VALUES
(1, 20040901, 0);

Student enrolled last year, and is currently enrolled. If students are
currently enrolled, the finish date is "0". Dates are kept as ]YYYYMMDD', eg
2004114 for Nov 14, 2004.

I want to be able to export the student name, most recent enrollment date, and
disenrollment date. I've successfully gotten the student name and most recent
enrollment date, but never the associated exit date.

This returns most recent enrollment date:
select students.name, max(enrollments.start) as start from students,
enrollments where enrollments.students_id=students.id group by students.name;

Now, to get the exit date, I've tried

select students.name,
max(enrollments.start) as start,
finish
from students, enrollments
where enrollments.students_id=students.id
AND max(enrollments.start)=enrollments.start
group by students.name, enrollments.finish

which results in "ERROR: Aggregates not allowed in WHERE clause" and also:

select students.name,
max(enrollments.start) as start,
finish
from students, enrollments
where enrollments.students_id=students.id
group by students.name, enrollments.finish
having enrollments.start=max(enrollments.start);

which returns "ERROR: Attribute enrollments.start must be GROUPed or used in
an aggregate function"

How can this be done? Can it be done?

-Ben
--
"The best way to predict the future is to invent it."
- XEROX PARC slogan, circa 1978

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-12-16 21:57:56 Re: Non-aggregate values attached to aggregates?
Previous Message Brian Kilpatrick 2004-12-16 21:22:58 Linux World Expo Boston booth