#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    8
    Rep Power
    0

    Oracle Returning Max value and value prior to max


    I have this query to return to me the latest case note, now I would like to add the date prior to the latest case note.

    select case_notes.applicant_id,
    reg.program_code,
    reg.last_name,
    reg.first_name,
    reg.reg_date,
    reg.region_code,
    reg.status_cd,
    MAX(case_notes.service_date) as "Last Case Note"
    from reg inner join case_notes on reg.applicant_id=case_notes.applicant_id
    where reg.status_cd='AC'
    group by case_notes.applicant_id, reg.program_code, reg.last_name, reg.first_name, reg.reg_date, reg.region_code, reg.status_cd
    order by MAX(case_notes.service_date)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    Code:
    select *
      from (select case_notes.applicant_id,
                   reg.program_code,
                   reg.last_name,
                   reg.first_name,
                   reg.reg_date,
                   reg.region_code,
                   reg.status_cd,
                   case_notes.service_date as "Last Case Note",
                   row_number () over(order by case_notes.service_date desc) as rn
              from reg 
              join case_notes 
                on reg.applicant_id = case_notes.applicant_id
             where reg.status_cd = 'AC') dt
     where rn <= 2

IMN logo majestic logo threadwatch logo seochat tools logo