From: Michael Fuhr <mike@fuhr.org>
To: Michael Avila <Michael.Avila.1@sbcglobal.net>
Cc: SQL PostgreSQL MailList <pgsql-sql@postgresql.org>
Subject: Re: Need SQL Help Finding Current Status of members
Date: Sat, 17 Dec 2005 17:26:10 -0700
Message-ID: <20051218002610.GA4832@winnie.fuhr.org> (raw)
In-Reply-To: <NBBBLNPHAMCFENDFHIDCGEOBEGAA.Michael.Avila.1@sbcglobal.net>
References: <NBBBLNPHAMCFENDFHIDCIENFEGAA.Michael.Avila.1@sbcglobal.net>
<NBBBLNPHAMCFENDFHIDCGEOBEGAA.Michael.Avila.1@sbcglobal.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, Michael.Avila.1@sbcglobal.net
Subject: Re: Need SQL Help Finding Current Status of members
In-Reply-To: <20051218002610.GA4832@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox