How to select a MAX record based on multiple results from a unique ID in MySQL

I run a report in mysql which returns all active members and their associated plan selections. If a member is terminated, this is displayed on the report as well. The issue I am facing, however is when a member simply decides to 'change' plans. Our system effective 'terminates' the original plan and starts fresh with the new one while keeping the original enrollment information for the member.

Sample report data may look like the following:

MemberID | Last    | First | Plan Code |  Original Effective | Change Date   |   Term Date
--------------------------------------------------------------------------------------------
12345    | Smith   | John  |     A     |   05-01-2011        |               |   06-01-2011
12345    | Smith   | John  |     B     |   06-01-2011        |               |      

In the example above, a member had plan code A from May 1, 2011 to June 1, 2011. On June 1, 2011, the member changed his coverage to Plan B (and is still active since no term or change data).

Ultimately, I need my report to generate the following instead of the 2 line items above:

MemberID | Last    | First | Plan Code |  Original Effective | Change Date   |   Term Date
----------------------------------------------------开发者_JAVA技巧----------------------------------------
12345    | Smith   | John  |     B     |   05-01-2011        |  06-01-2011   | 

which shows his original plan effective date, the date of the change, and leaves the termination field blank.

I know I can get into a single row by using Group By the Member ID and I can even add the change date into the appropriate field with a (IF COUNT() > 1) statement but I am not able to figure out how to show the correct plan code (C) or how to leave the term date blank keeping the original effective date.

Any ideas?

Although I hate columns with embedded spaces and having to tick them, this should do it for you. The PreQuery will detect how many policy entries there are, preserve the first and last too, grouped by member. Once THAT is done, it can re-join the the plan enrollment on the same member but matching ONLY for the last "Original Effective" date for the person... That will give you the correct Plan Code. Additionally, if the person was terminated, it's date would be filled in for you. If still employed, it will be blank on that record.

select STRAIGHT_JOIN
      pe2.MemberID,
      pe2.Last,
      pe2.First,
      pe2.`Plan Code`
      PreQuery.PlanStarted `Original Effective`,
      case when PreQuery.PlanEntries > 1 then PreQuery.LastChange end `Change Date`,
      pe2.`Term Date`
   from
      ( select 
              pe.MemberID,
              count(*) as PlanEntries,
              min( `pe`.`Original Effective` ) PlanStarted,
              max( `pe`.`Original Effective`) LastChange
           from
              PlanEnrollment pe
           group by
              pe.MemberID ) PreQuery

     join PlanEnrollment pe2
        on PreQuery.MemberID = pe2.MemberID
        AND PreQuery.LastChange = pe2.`Original Effective`

I remember having the problem, but not coming up with a GROUP BY-based solution ;) Instead, I think you can do something like

SELECT memberid, plancode 
FROM members 
INNER JOIN plans ON members.id=plans.member_id 
WHERE plans.id IN 
  (SELECT id FROM plans 
   WHERE member_id = members.id 
   ORDER BY date DESC 
   LIMIT 0,1)