Non-aggregate values attached to aggregates?
От | Benjamin Smith |
---|---|
Тема | Non-aggregate values attached to aggregates? |
Дата | |
Msg-id | 200412161338.19746.lists@benjamindsmith.com обсуждение исходный текст |
Ответы |
Re: Non-aggregate values attached to aggregates?
Re: Non-aggregate values attached to aggregates? Re: Non-aggregate values attached to aggregates? |
Список | 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
В списке pgsql-general по дате отправления: