Non-aggregate values attached to aggregates?

Поиск
Список
Период
Сортировка
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Richard_D_Levine@raytheon.com
Дата:
Сообщение: Re: pl/pgsql oddity
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Non-aggregate values attached to aggregates?