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
