Oracle to retrieve maximum record

Question

Table_A

A_id          
1     



Tale_B
B_id       A_id
1            1
2            1
3            1

Table_C

B_id      Process_date
1   20130101 12:20:01
2   20130101 12:10:01
3   20130101 13:00:01

How to retrieve the maximum process_date from Table_C with references of Table_A A_id based on Table_C timing window.If i want to retrieve Table_C id and max(process_date) in timing window 20130101 12:09:00 to 12:21:00 then it should return id as 1 and process_date as 12:20:01

You should try something and come back to ask why you're not getting the expected result. [FAQ](http://stackoverflow.com/faq) will help you to post your question on the right way. Good luck! — Luc M, Mar 05 '13 at 21:33

Taryn · Accepted Answer · 2013-03-05T22:02:55.183

1

You can use a subquery that gets the max(process_date):

select c1.b_id,
  c2.MaxDate
from table_a a
inner join table_b b
  on a.a_id = b.a_id
inner join table_c c1
  on b.b_id = c1.b_id
inner join
(
  select max(process_date) MaxDate
  from table_c
) c2
  on c1.process_date = c2.maxdate;

See SQL Fiddle with Demo

Or you can use row_number():

select b_id, process_date
from 
(
  select c1.b_id,
    c1.process_date,
    row_number() over(partition by a.a_id order by c1.process_date desc) rn
  from table_a a
  inner join table_b b
    on a.a_id = b.a_id
  inner join table_c c1
    on b.b_id = c1.b_id
) 
where rn = 1

See SQL Fiddle with Demo

edited Mar 05 '13 at 22:02

answered Mar 05 '13 at 21:36

Taryn

242,637
56
362
405

:Thanks,it is giving duplicated when i retrieve b_id,process_date – user2127414 Mar 05 '13 at 21:50
@user2127414 can you expand your sample data? And then show the desired result? – Taryn Mar 05 '13 at 21:51
I want 3,13:00:01 from Table_c – user2127414 Mar 05 '13 at 21:58
@user2127414 see my edit, I was returning `a_id` but you just need to change it to `b_id` and you will get the result. – Taryn Mar 05 '13 at 22:03
i added this condition to retrieve first record from table c ( TO_CHAR(src.PROCESS_DATE,'YYYYMMDD HH24:MI:SS') between ('20130228'||' '|| '12:11:01') and ('20130301'||' '||'12:21:00') ).It is not giving expected result : 1 and 20130101 – user2127414 Mar 05 '13 at 22:13
what is datatype of process_date? – Taryn Mar 05 '13 at 22:18
It should return id and Max(process_date) from table in that timing window – user2127414 Mar 05 '13 at 22:23
i have 20 lakhs records in each table,query is very slow with row_num solution.Indexes already present.How to improve the performance? – user2127414 Mar 06 '13 at 21:29
@user2127414 Please post a new question with your issues. – Taryn Mar 06 '13 at 21:54
Only issue is performance, how to use rownum with above requirement – user2127414 Mar 06 '13 at 22:10
Raised different question : http://stackoverflow.com/questions/15259989/how-to-improve-oracle-row-number-in-query – user2127414 Mar 06 '13 at 23:11

JodyT · Answer 2 · 2013-03-05T22:07:38.647

0

This query should get you the max process_date for each B_id with a reference in Table_A:

SELECT c.B_id, MAX(Process_date)
FROM Table_C c
INNER JOIN Table_B b 
    ON b.B_id = c.B_id
INNER JOIN Table_A a
    ON a.A_ID = b.A_id
GROUP BY c.B_id

Just remove the c.B_id from the select and the GROUP BY if you want the max process_date of all B_id's with a reference to Table_A.

SQL Fiddle

edited Mar 05 '13 at 22:07

answered Mar 05 '13 at 21:44

JodyT

4,324
2
19
31

Oracle to retrieve maximum record

2 Answers2

Linked