Posted by Ed Murphy on 09/30/67 12:00

t8ntboy wrote:

> Is it possible to exclude courses if there most recent status is not
> ('DEPTSETUP', 'DEPTQUEUE', 'CERTRN',
> 'DEPTCORRREQ')?
>
> For instance,
>
> A course record may look like this:
>
> Course Status Date
> BIOL 101 DEPTSETUP 5/5/2007
> BIOL 101 CERTRN 6/30/2007
> BIOL 101 REMOVE 7/8/2007
>
>
> Since the status REMOVE is the most recent I want the course to be
> excluded from the results.
>
> The SQL statement above overlooks REMOVE since it is not part of the
> IN clause even though it is the most recent status.

If you have something like this that's used a lot, you may want to
wrap it in a view, e.g.

create view CourseCurrentStatus as
select Course, Status
from Courses c1
where ActionDate = (
select max(ActionDate)
from Courses c2
where c2.Course = c1.Course
)

and then JOIN to that view (same as you would to a table).

[Back to original message]