Select two MAX record per each

veeruu · January 21st, 2006, 01:13 AM

Hi!

I would be very happy if anybody could help me in the following.

table contains the following fields,
    ConsultantID
    ConsultantType
    StartDate
    ExpDate
    UpdatedDate
    UpdatedBy
    UpdatedInfo
The tables allows same IDs(more than One) with diff. data with new UpdatedDate
I need a query to display .. MAX Two Reords based on Updated date for all IDs. this is my basic one.
and after getting the two records for each consultantID,
I need to display the secondMax record only if the defference between
the secondMax record (ExpDate) and FirstMAx record (Startdate) is less than 25.

Im working on this qurey from last 3 days ...
Plz.. help me to get the solution...

Thanks & Regards,
Veeru.

jbenson001 · January 21st, 2006, 05:50 PM

show some sample data and your desired resutls. This will help us help you

Jim

veeruu · January 23rd, 2006, 12:57 AM

Hi, This is My sample Data ...
Plz. help me....
CID CType StartDate ExpDate UpdatedDate
--- ---- -(MM/DD/YYYY)- ----------- ------------
1 B1 01/01/2004 03/03/2004 01/02/2004
1 OPT 03/06/2004 03/03/2005 03/10/2004
1 H1 03/01/2005 01/03/2008 03/04/2005
2 B1 10/01/2004 01/09/2005 10/04/2004
2 OPT 01/06/2005 01/03/2006 03/07/2006
2 H1 01/01/2006 01/03/2009 01/04/2006

I need a select query to display the result as follows..

1 H1 03/01/2005 01/03/2008 03/04/2005
2 OPT 10/06/2005 01/03/2006 10/07/2006
2 H1 01/01/2006 01/03/2009 01/04/2006

i.e. 2 MAX Updated records for each CID.
And display Second Record if diff. between
Second Exp.Date and Current Date is 25.

Thanks & Regards,
Veeru.