Get the second last ID by date, based on the current ID-CodePudding

I have a table something like:

What i'm trying to achieve is getting the last TARGET_CNPJ, Based on the END_DATE (Like get the last record before the current):

CNPJ	TARGET_CNPJ	END_DATE	LAST_CNPJ
05775774000108	01638542000157	2012-03-12	No Previous TARGET_CNPJ
05775774000108	62418140000131	2014-03-12	01638542000157
05775774000108	59281253000123		62418140000131
10951930000184	02201501000161	2010-04-26	No Previous TARGET_CNPJ
10951930000184	62285390000140	2010-05-25	02201501000161
10951930000184	61809182000130		61809182000130

Is it possible to achive it via MySQL Query?

Thanks!!

CodePudding user response：

Try:

SELECT
    CNPJ,
    TARGET_CNPJ,
    END_DATE,
    LAG(TARGET_CNPJ) OVER (ORDER BY COALESCE(END_DATE, NOW())) LAST_CNPJ
FROM MyTable