1

The first column (CALL_START) is time on which somebody of some particular ID (CALLER_ID) made a call. see snippet of data here.

Now I want to pick out the next call of the same ID and store it in a new column named as 'Next_call' and if there is no next call it stores NULL.

So this is the result that I want below.

CALL_START             NEXT_CALL             CALLER_ID
2017-01-14 10:50:38   NULL                  2837061
2017-01-14 10:21:03   2017-01-14 10:50:38   2837061

The CALLER_ID should be same to find the next call, so I have to find a next call according to each ID.

I have tried this so far.

select CALL_START, CALLER_ID
from call_data.records
where CALL_START > (select CALL_START from call_data.records group by CALLER_ID)
;

This query does not work for me and I am not sure how to do it exactly as I am learning sql records is a temp table which I joined using different table's data.

I am new to learning SQL, so any help would be greatly appreciated.

GMB
  • 216,147
  • 25
  • 84
  • 135
Chloe
  • 127
  • 1
  • 12

1 Answers1

0

If you are running MySQL 8.0, you can use lead()|:

select 
    call_start, 
    lead(call_start) over(partition by caller_id order by call_start) next_call, 
    caller_id
from call_data.records r

In earlier versions, one alternative is a subquery:

select
    call_start,
    (
        select min(r1.call_start) 
        from call_data.records r1 
        where r1.caller_id = r.caller_id and r1.call_start > r.call_start
    )  next_call,
    caller_id
from call_data.records r
GMB
  • 216,147
  • 25
  • 84
  • 135
  • The first query gives me the next_call column but the caller_id shows null in all rows – Chloe Sep 17 '20 at 11:36
  • Please tell me why are there 2 from statements in the second query? It shows me a syntax error. – Chloe Sep 17 '20 at 11:37
  • @Chloe: that was a typo. Fixed – GMB Sep 17 '20 at 11:46
  • I don't think this can work as records is a temporary table. I mentioned above. Can you tell me an alternate way of LEAD() using rownumber? – Chloe Sep 17 '20 at 12:55