Post reply

Getting second last records

  • November 21, 2006 at 10:35 pm

    #121581

    Here have got 2 tables, one is Customer table and another is Call_Results table. Relation between Customer table and Call_Results table is One-To-Many and is related with RecID.

    Here Call_Results ID will always be incremental, so latest Call_Results ID is the last call result, but I would like to get Second from the last. Any help in this direction is of great help. Thanks.

  • November 22, 2006 at 2:58 am

    #673399

    You didn't post the DDL, so you'll probably have to modify the column names - but the idea should be obvious. Generally, you want to find the highest CallID that is lower than the last one.

    SELECT cr.custID, MAX(resultID)

    FROM call_results cr

    JOIN

    (select c.custID, MAX(r.resultID) as maxID

    from customers c

    join call_results r on r.custID = c.custID

    group by c.custID) maxcall ON maxcall.custID=cr.custID

    WHERE cr.resultID < maxcall.maxID

    GROUP BY cr.custID

  • November 22, 2006 at 3:59 am

    #673412

    This is for the N th maximum . Please replace N with 2 .

    please try this.

    select call_Result_id from Customer c, Call_Results CR1

    where c.custId=CR1.custId

    and (N-1)=(Select count(distinct (Call_Result_id)) from Call_Results CR2 where c.custId=CR2.custId and

    CR2.call_result_id>CR1.call_result_id)

    Kindest Regards,

    Anthony Surendran

  • November 22, 2006 at 8:17 pm

    #673629

    Thanks your valuable input. In fact "N" will vary from customer to customer. In other words, one customer will have 4 call results where as other customer might have 10 call results.

  • November 22, 2006 at 11:18 pm

    #673640

    Here "N" is not the number of calls.

    This is for finding the N th maximum. we have the join to deal with the one to many relation ship. so Just replace the N with 2 which will yield 1 as a result, and the query will work fine.

    Kindest Regards,

    Anthony Surendran

  • November 22, 2006 at 11:43 pm

    #673643

    Thanks for the help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply