From: "Michael Avila" <Michael.Avila.1@sbcglobal.net>
To: "SQL PostgreSQL MailList" <pgsql-sql@postgresql.org>
Subject: Re: Need SQL Help Finding Current Status of members
Date: Sat, 17 Dec 2005 19:34:22 -0500
Message-ID: <NBBBLNPHAMCFENDFHIDCOEOIEGAA.Michael.Avila.1@sbcglobal.net>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
In-reply-to: <20051218002610.GA4832@winnie.fuhr.org>
Importance: Normal

The table is not empty. I did some playing around with the SQL Statement but
got no where. So I added a record status column that will be non-NULL when
the status becomes history because a new status is added. Yeah, I cheated to
make it easy!! LOL But it now works and that is what counts!

Thanks for the help.

Mike


> -----Original Message-----
> From: Michael Fuhr [mailto:mike@fuhr.org]
> Sent: Saturday, December 17, 2005 7:26 PM
> To: Michael Avila
> Cc: SQL PostgreSQL MailList
> Subject: Re: [SQL] 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