CODEWITHSUNDEEP
oracle-database
user3178723
user3178723

Reputation: 11

Having issues in using the Oracle Rank function

I have table call_detail with following columns phone_no, call_id, call_date. I want to create a view with every row and the previous made call from each record. How can I achieve that?

I have tried using rank with a self join on the table call_detail but that gives me only the latest record from the table.

Upvotes: 0

Views: 131

Answers (1)

Alex Poole
Alex Poole

Reputation: 191315

I'm not sure what you're doing with lag() but it doesn't sound right. It sounds like exactly what you're after; from the documentation:

LAG is an analytic function. It provides access to more than one row of a table at the same time without a self join.

You want the previous call for each phone number, so you need to partition by phone_no, and order by call_date. This shows the previous value for all three fields:

select phone_no, call_id, call_date,
  lag(phone_no) over (partition by phone_no order by call_date)
    as prev_phone_no,
  lag(call_id) over (partition by phone_no order by call_date)
    as prev_call_id,
  lag(call_date) over (partition by phone_no order by call_date)
    as prev_call_date
from call_detail
order by call_date;

With the sample data you gave in a comment this gives:

PHONE_NO          CALL_ID CALL_DATE           PREV_PHONE_NO  PREV_CALL_ID PREV_CALL_DATE    
-------------- ---------- ------------------- -------------- ------------ -------------------
234534534               4 2013-01-11 20:11:03                                                 
234534534               6 2013-01-11 20:11:03 234534534                 4 2013-01-11 20:11:03 
234556777               2 2013-01-11 20:11:03                                                 
234543453               7 2013-01-12 15:11:03                                                 
234543453               5 2013-01-12 16:11:03 234543453                 7 2013-01-12 15:11:03 
234543453               3 2013-01-12 18:11:03 234543453                 5 2013-01-12 16:11:03 
234543453               1 2013-01-12 20:11:03 234543453                 3 2013-01-12 18:11:03

SQL Fiddle

Which you can use to create a view (without an order by clause, and with only the columns you want).

Upvotes: 1

Related Questions