Re: Non-aggregate values attached to aggregates?

On Thu, 16 Dec 2004 13:38:19 -0800, Benjamin Smith <lists(at)benjamindsmith(dot)com> wrote:
> 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.

just subselect the max date for the student_id in the where clause.

select students.name,
enrollments.start,
enrollments.finish
>from students, enrollments
where enrollments.students_id = students.id
and enrollments.start =
(select max(e1.start)
from enrollments e1
where e1.student_id = students.id)

klint.

+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg(at)kgb(dot)une(dot)edu(dot)au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+