X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id C2E7C9DC81B for ; Thu, 15 Dec 2005 21:30:54 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 21182-09 for ; Thu, 15 Dec 2005 21:30:57 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp108.sbc.mail.re2.yahoo.com (smtp108.sbc.mail.re2.yahoo.com [68.142.229.97]) by postgresql.org (Postfix) with SMTP id 97E6D9DC84D for ; Thu, 15 Dec 2005 21:30:51 -0400 (AST) Received: (qmail 84008 invoked from network); 16 Dec 2005 01:30:54 -0000 Received: from unknown (HELO epox2800) (michael.avila.1@sbcglobal.net@70.228.56.50 with login) by smtp108.sbc.mail.re2.yahoo.com with SMTP; 16 Dec 2005 01:30:54 -0000 From: "Michael Avila" To: "SQL PostgreSQL MailList" Subject: Need SQL Help Finding Current Status of members Date: Thu, 15 Dec 2005 20:31:09 -0500 Message-ID: MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_001B_01C601B6.7BBA4B40" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670 X-MS-TNEF-Correlator: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=2.798 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_POST=1.44, DNS_FROM_RFC_WHOIS=0.879] X-Spam-Score: 2.798 X-Spam-Level: ** X-Archive-Number: 200512/135 X-Sequence-Number: 23677 This is a multi-part message in MIME format. ------=_NextPart_000_001B_01C601B6.7BBA4B40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_001B_01C601B6.7BBA4B40 Content-Type: application/ms-tnef; name="winmail.dat" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="winmail.dat" eJ8+IgkBAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEGgAMADgAAANUHDAAPABQAHwAAAAQALgEB A5AGAAQHAAAiAAAACwACAAEAAAALACMAAAAAAAMAJgAAAAAACwApAAAAAAADADYAAAAAAB4AcAAB AAAAMAAAAE5lZWQgU1FMIEhlbHAgRmluZGluZyBDdXJyZW50IFN0YXR1cyBvZiBtZW1iZXJzAAIB cQABAAAAFgAAAAHGAeBkW+KsatrVm0EGgcvBPILbFcAAAAIBHQwBAAAAIwAAAFNNVFA6TUlDSEFF TC5BVklMQS4xQFNCQ0dMT0JBTC5ORVQAAAsAAQ4AAAAAQAAGDgAit17gAcYBAgEKDgEAAAAYAAAA AAAAAEhgHmF4MtERvfdERVNUAADCgAAACwAfDgEAAAACAQkQAQAAACsDAAAnAwAACgUAAExaRnXK h9DJAwAKAHJjcGcxMjUWMgD4C2BuDhAwMzNPAfcCpAPjAgBjaArAc/BldDAgBxMCgwBQAvKvEMkI VQeyAoB9CoF2CJCkd2sLgGQ0DGBjAFAjCwMLtSBJIBDwdmUoIGEgAZFsFjB3aFEN4GggawngcAQg dEJyANBrIG9mFmBo5RYwcwGQdHUEIBfxB4DzBtAEkHMuFdADoBgiFnRfBAAK4wqEGtoVQTEY9V+M aWQaUAIwKDgpGpQtGGRfBaABACAQ4igxPR08ZBiAFjAfoxqaS0XqWRw5KBxHLB2pIoYfoucdNRr/ ArEgRQDQFxAZBIceUAORFgNtdWx0BSD3FqEJcAWhZAQgGTAmgBigZxYyJ6QaUmFkAQAcwGV/JdIn QAeAGBoQ4Q8gB5Fi/nUFQBgiBvAcwCjIFzAFMZ8CEAXAFuAYYAWweS4kGm5XEPAFQBXgdwBwK7Fv 9x/gL5AaYWYUgRgTC2AfwD8YYBhWLUIpwxkEGXBBY/MYkAdAbHkvCTATMtEYEX5vKHED8BggFkAD oBhoIvRBIhlwQiuhNMAnATDhuxkwGBNjCHAJcC9RKDCk5ikYVRlwSG8H4C+xFeD/MBcEYDDhN1Yf ozGNHNEWQf5wAxAr8RjhAHAzACemLUL/PPMZBTSkPPMYZREhJ0APIMc+tQIgFjBTUUwYUxkQ8TeR PyBULYEpIygQNmHtGTB5AiAcwG0zACaACrA9QpBsNMAIkD/BOTJhbRUWQHMUcWctNGVscP0t+00z AAuAQ+EHQEDzGm0AU0VMRUNUICrgIEZST00DMRwmGGSBJYIgV0hFUkUYVcEeBD0gJ2EnJBorlO8u 4RphQ0FD0G00wBlxFyDGbjjxA5FBTkQeUANw+weRQNB4NsEroUDQKZFFcm8WQAGAE3FNAiFOFk0D TfxBWBpSTlAtJB+iRCNNBj8IYE3yF3EYMAlwFkFMQehURVNIsERVIUHQFeD+ZByxUoIRICvxQNEW 0Anw9y8DBCAJAG9E4y7hGCI61HR1bjKgaQIgGWAkGkF8cHAnoQcwH8EYIkV/TS5pFzAkGhPxAF2Q AAsAAIAIIAYAAAAAAMAAAAAAAABGAAAAAAOFAAAAAAAAAwACgAggBgAAAAAAwAAAAAAAAEYAAAAA EIUAAAAAAAADAAWACCAGAAAAAADAAAAAAAAARgAAAABShQAAc3kBAAMACYAIIAYAAAAAAMAAAAAA AABGAAAAAAGFAAAAAAAAHgASgAggBgAAAAAAwAAAAAAAAEYAAAAAVIUAAAEAAAAEAAAAOS4wAAsA FoAIIAYAAAAAAMAAAAAAAABGAAAAAA6FAAAAAAAAAwAXgAggBgAAAAAAwAAAAAAAAEYAAAAAEYUA AAAAAAADABmACCAGAAAAAADAAAAAAAAARgAAAAAYhQAAAAAAAAsABIEIIAYAAAAAAMAAAAAAAABG AAAAAAaFAAAAAAAACwAIgQggBgAAAAAAwAAAAAAAAEYAAAAAgoUAAAEAAAACAfgPAQAAABAAAABI YB5heDLREb33REVTVAAAAgH6DwEAAAAQAAAASGAeYXgy0RG990RFU1QAAAIB+w8BAAAAVAAAAAAA AAA4obsQBeUQGqG7CAArKlbCAABQU1RQUlguRExMAAAAAAAAAABOSVRB+b+4AQCqADfZbgAAAEU6 XE91dGxvb2tEYXRhXG91dGxvb2sucHN0AAMA/g8FAAAAAwANNP03AAACAX8AAQAAAD0AAAA8TkJC QkxOUEhBTUNGRU5ERkhJRENHRU5CRUdBQS5NaWNoYWVsLkF2aWxhLjFAc2JjZ2xvYmFsLm5ldD4A AAAAAwAGEDnjH2YDAAcQCAMAAAMAEBAAAAAAAwAREAAAAAAeAAgQAQAAAGUAAABJSEFWRUFUQUJM RVdISUNIS0VFUFNUUkFDS09GVEhFU1RBVFVTT0ZNRU1CRVJTSU5USEVUQUJMRUlTTUVNQkVSSURJ TlQoOClTVEFUVVNDT0RFQ0hBUigxKVNUQVRVU0RBVEVEAAAAACqp ------=_NextPart_000_001B_01C601B6.7BBA4B40--