From: Michael Avila <Michael.Avila.1@sbcglobal.net>
To: SQL PostgreSQL MailList <pgsql-sql@postgresql.org>
Subject: Need SQL Help Finding Current Status of members
Date: Thu, 15 Dec 2005 20:31:09 -0500
Message-ID: <NBBBLNPHAMCFENDFHIDCGENBEGAA.Michael.Avila.1@sbcglobal.net> (raw)
I have a table which keeps track of the status of members. In the table is
member_id int(8)
status_code char(1)
status_date date
KEY member_id (member_id,status_code,status_date)
Each member can have multiple records because a record is added each time
the status changes but the old record is kept for history.
What I want to do is find the latest status for each member. Actually I want
to find all those with an status of "A". But it must be the current (latest)
status. How do I find the most current date for each member in a pile of
many records for many members with many status settings with one SQL
statement? This is a bit beyond my capabilities so I am asking for help.
My initial SQL is
SELECT * FROM memberstatus WHERE status_code = 'a'
but that is my limit. I know an AND comes next but need help after that! I
know that MAX is not for dates so that is out. Is there a LATEST DATE? I did
not see one when I was looking at the date functions.
Appreciate the help.
Mike
Attachments:
winmail.dat (application/ms-tnef, 1.9K) download
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: Michael.Avila.1@sbcglobal.net
Subject: Re: Need SQL Help Finding Current Status of members
In-Reply-To: <NBBBLNPHAMCFENDFHIDCGENBEGAA.Michael.Avila.1@sbcglobal.net>
* 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