Search code examples
mysqlnulllag

How to get the last non null value from the MYSQL table with the LAG

I'm tying a LAG function to retrieve the last non null value from the table to replace the null values in a row. The code I'm using:

SELECT ticket_id, business_area, priority ,client_name, 
        closed_date, closed_date_id,Next_Create_date, 
        case 
            when Next_Create_date is null and Ticket_ID = 0 
            then LAG(Next_Create_date) 
                over (partition by ticket_id, business_area, 
                            priority, client_name, 
                            closed_date, Closed_Date_ID 
                        order by Next_Create_date desc 
                     ) 
            else Next_Create_date 
        end  Next_Create_date2
from ams_auto.No_Issue_Time_NM a
order by closed_date desc ;

And these are the results:

enter image description here

Every yellow Next_Created_Date2 field should really have the last NON null value. In this case that would be '2021-07-16 00:44:17' but for some reason it's not working.

Any ideas on how to achieve this?

Sample data:

ticket_id business_area priority client_name closed_date Next_Create_date
61622 RMS Severity 3 TB 7/22/2021 22:49 8/29/2021 0:46
0 RMS Severity 1 TB 7/19/2021 2:00 8/29/2021 0:46
0 RMS Severity 2 TB 7/19/2021 2:00 8/29/2021 0:46
0 RMS Severity 1 TB 7/12/2021 2:00 8/29/2021 0:46
0 RMS Severity 2 TB 7/12/2021 2:00 8/29/2021 0:46
0 RMS Severity 3 TB 7/12/2021 2:00 8/29/2021 0:46
0 RMS Severity 1 TB 7/5/2021 2:00 8/29/2021 0:46
0 RMS Severity 2 TB 7/5/2021 2:00 8/29/2021 0:46
0 RMS Severity 3 TB 7/5/2021 2:00 8/29/2021 0:46
0 RMS Severity 1 TB 6/28/2021 2:00 null
0 RMS Severity 2 TB 6/28/2021 2:00 null
0 RMS Severity 3 TB 6/28/2021 2:00 null
0 RMS Severity 1 TB 6/21/2021 2:00 null
0 RMS Severity 2 TB 6/21/2021 2:00 null
0 RMS Severity 3 TB 6/21/2021 2:00 null
0 RMS Severity 1 TB 6/14/2021 2:00 null
0 RMS Severity 2 TB 6/14/2021 2:00 null
0 RMS Severity 3 TB 6/14/2021 2:00 null
0 RMS Severity 1 TB 6/7/2021 2:00 null
0 RMS Severity 2 TB 6/7/2021 2:00 null
0 RMS Severity 3 TB 6/7/2021 2:00 null
0 RMS Severity 1 TB 5/31/2021 2:00 7/16/2021 0:44
0 RMS Severity 2 TB 5/31/2021 2:00 7/16/2021 0:44
0 RMS Severity 3 TB 5/31/2021 2:00 7/16/2021 0:44
57765 RMS Severity 3 TB 5/28/2021 2:35 7/16/2021 0:44
57615 RMS Severity 3 TB 5/27/2021 2:24 5/28/2021 0:56

And this is the desired result:

ticket_id business_area priority client_name closed_date Next_Create_date
61622 RMS Severity 3 TB 7/22/2021 22:49 8/29/2021 0:46
0 RMS Severity 1 TB 7/19/2021 2:00 8/29/2021 0:46
0 RMS Severity 2 TB 7/19/2021 2:00 8/29/2021 0:46
0 RMS Severity 1 TB 7/12/2021 2:00 8/29/2021 0:46
0 RMS Severity 2 TB 7/12/2021 2:00 8/29/2021 0:46
0 RMS Severity 3 TB 7/12/2021 2:00 8/29/2021 0:46
0 RMS Severity 1 TB 7/5/2021 2:00 8/29/2021 0:46
0 RMS Severity 2 TB 7/5/2021 2:00 8/29/2021 0:46
0 RMS Severity 3 TB 7/5/2021 2:00 8/29/2021 0:46
0 RMS Severity 1 TB 6/28/2021 2:00 7/16/2021 0:44
0 RMS Severity 2 TB 6/28/2021 2:00 7/16/2021 0:44
0 RMS Severity 3 TB 6/28/2021 2:00 7/16/2021 0:44
0 RMS Severity 1 TB 6/21/2021 2:00 7/16/2021 0:44
0 RMS Severity 2 TB 6/21/2021 2:00 7/16/2021 0:44
0 RMS Severity 3 TB 6/21/2021 2:00 7/16/2021 0:44
0 RMS Severity 1 TB 6/14/2021 2:00 7/16/2021 0:44
0 RMS Severity 2 TB 6/14/2021 2:00 7/16/2021 0:44
0 RMS Severity 3 TB 6/14/2021 2:00 7/16/2021 0:44
0 RMS Severity 1 TB 6/7/2021 2:00 7/16/2021 0:44
0 RMS Severity 2 TB 6/7/2021 2:00 7/16/2021 0:44
0 RMS Severity 3 TB 6/7/2021 2:00 7/16/2021 0:44
0 RMS Severity 1 TB 5/31/2021 2:00 7/16/2021 0:44
0 RMS Severity 2 TB 5/31/2021 2:00 7/16/2021 0:44
0 RMS Severity 3 TB 5/31/2021 2:00 7/16/2021 0:44
57765 RMS Severity 3 TB 5/28/2021 2:35 7/16/2021 0:44
57615 RMS Severity 3 TB 5/27/2021 2:24 5/28/2021 0:56

Kind regards, Rosa

Solution

  • I don't see any test data to play with, so here is an simple example

    with qq(q) as (
  select 1 union all
  select 2 union all
  select null union all
  select 3
)

select q, 
       lag(q, 1, 'nothing to show') over(order by q desc)
  from qq;

    You may sort data and move nulls in the end - just sort it descending (don't ask me why).

    Here is the result

    q lag
    1 nothing to show
    2 1
    3 2
    3

    Here is the dbfiddle to play with

    UPD. For the test case uploaded there is no need to use LAG but you may find subqueries useful here

    select td.ticket_id,
       td.business_area,
       td.priority,
       td.client_name,
       td.closed_date,
       ifnull(td.next_create_date, (select min(next_create_date) 
                                      from test_data td_1
                                     where td_1.ticket_id = td.ticket_id
                                       and td_1.business_area = td.business_area
                                       and td_1.priority = td.priority
                                       and td_1.client_name = td.client_name
                                       and td_1.next_create_date > td.closed_date) 
             ) next_creation_date_fixed
  from test_data td
 order by closed_date desc;

    One thing to mention is subqueries often lead to performance degradataion, so if you're on mySQL >= 8.0.14 you may find lateral joins useful. See here for details

    dbfiddle