Re: Need SQL Help Finding Current Status of members

On Fri, Dec 16, 2005 at 07:44:46PM -0500, Michael Avila wrote:
> Just tried it and it returned nothing.
> 
> > > Select * from memberstatus A where not exists
> > >    (select * from emberstatus B where B.member_id=A.member_id and
> > > B.status_date >A.status_date)
> 
> Why the WHERE NOT EXISTS?

The query selects each row in memberstatus for which no other rows
(WHERE NOT EXISTS) with the same member_id have a later status_date;
in other words, the row(s) with the latest status_date for each
member_id.  For example, given
member_id | status_code | status_date 
-----------+-------------+-------------        1 | a           | 2005-12-01        1 | b           | 2005-12-02
1| c           | 2005-12-03        2 | x           | 2005-12-11        2 | y           | 2005-12-12        2 | z
  | 2005-12-13
 

the query should return
member_id | status_code | status_date 
-----------+-------------+-------------        1 | c           | 2005-12-03        2 | z           | 2005-12-13

Offhand I can't think of why the query would return nothing unless
the table is empty, but maybe I'm overlooking something or making
unwarranted assumptions about the data.  Can you post a sample data
set for which the query returns no rows?

-- 
Michael Fuhr