Returning max value and value prior

Question

I have a query I run to tell me the latest note for active participants:

select notes.applicant_id,
   reg.program_code,
   reg.last_name,
   reg.first_name,
   reg.status_cd,
   MAX(notes.service_date) as "Last Note"
from reg inner join notes on reg.applicant_id=notes.applicant_id
where reg.status_cd='AC'
group by notes.applicant_id, reg.program_code, 
         reg.last_name, reg.first_name, reg.reg_date, 
         reg.region_code, reg.status_cd
order by MAX(notes.service_date)

But I would also like this query to give me the result of the note.service_date just prior to the max service_date as well.

Results would look like this

notes.applicant_id   reg.last_name  reg.first_name reg.status_cd  Last Note    Prior Note
 12345                 Johnson          Lori           AC        01-NOV-2011   01-OCT-2011

I am working in oracle.

If you are using Oracle, please tag the question as such. – Jonathon Reinhart Nov 05 '12 at 14:00 — Jonathon Reinhart, Nov 05 '12 at 14:00
You're trying to mix aggregate and non aggregate values... – YXD Nov 05 '12 at 14:22 — YXD, Nov 05 '12 at 14:22

A.B.Cade · Accepted Answer · 2012-11-05T14:48:43.200

You can use the lag function, or join it with the same table.

Here is a simpler example (you haven't givven us data sample):

create table t as
(select level as id, mod(level , 3) grp, sysdate - level dt
from dual 
connect by level < 100
)

and here are the queries:

select t2.grp,t1.grp, max(t1.dt) mdt, max(t2.dt) pdt
  from t t1
  join t t2 on t1.dt < t2.dt and t1.grp = t2.grp
 group by t2.grp, t1.grp;

or

select grp, max(pdt), max(dt)
 from(
 select grp, lag(dt) over (partition by grp order by dt) pdt, dt 
 from t)
 group by grp

Here is a fiddle

In your case it could be something like this:

select t.applicant_id, t.program_code, 
         t.last_name, t.first_name, t.reg_date, 
         t.region_code, t.status_cd,
         max(t.dt) as "Last Note",
         max(t.pdt) as "Prev Note"
from (
select notes.applicant_id,
   reg.program_code,
   reg.last_name,
   reg.first_name,
   reg.status_cd,
   notes.service_date as dt,
   lag(notes.service_date)  over (partition by notes.applicant_id,
   reg.program_code,
   reg.last_name,
   reg.first_name,
   reg.status_cd order by notes.service_date) as pdt
from reg inner join notes on reg.applicant_id=notes.applicant_id
where reg.status_cd='AC'
) t
group by t.applicant_id, t.program_code, 
         t.last_name, t.first_name, t.reg_date, 
         t.region_code, t.status_cd
order by MAX(t.dt)

I don't have the capability to create tables. I only get to retrieve data. — user1466935, Nov 05 '12 at 15:24
@user1466935, you don't need to create tables - this was just a simple example. Use the same idea on **your** table. See the query at the buttom of the answer — A.B.Cade, Nov 05 '12 at 15:45
A.B. Cade---Thank you for answering this question. I'm now interested in seeing the author of the notes.service_date for that Last Note. Is it possible to use lag on two different columns within the same query? Since notes can be put in by any user, I would need the author of the t.dt column. The column name is notes.author . Would appreciate any help you could give, I've been studying this today to no avail. Thank you. — user1466935, Feb 20 '14 at 20:26

score 0 · Answer 2 · answered Nov 05 '12 at 14:33

0

If I understand you correctly, here's one way to do it:

SELECT *
  FROM (select notes.applicant_id,
               reg.program_code,
               reg.last_name,
               reg.first_name,
               reg.status_cd,
               notes.service_date AS "Last Note",
               ROW_NUMBER() OVER (PARTITION BY notes.applicant_id, reg.program_code, 
                   reg.last_name, reg.first_name, reg.reg_date, reg.region_code, 
                   reg.status_cd ORDER BY notes.service_date DESC) rn
          from reg inner join notes on reg.applicant_id=notes.applicant_id
         where reg.status_cd='AC')
 WHERE rn < 3;

answered Nov 05 '12 at 14:33

DCookie

42,630
11
83
92

Works! Thank you! I never thought about using row and partition. – user1466935 Nov 05 '12 at 14:54
Would there be anyway to put all this in one row by notes.applicant.id ? Right now I see 2 rows of information for each person, it would be great if I could see it in just one row. – user1466935 Nov 05 '12 at 15:12

Returning max value and value prior

2 Answers2