CODEWITHSUNDEEP
mysql
Bruno Aarão
Bruno Aarão

Reputation: 47

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

I have a table something like:

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

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!!

Upvotes: 3

Views: 39

Answers (1)

Michał Turczyn
Michał Turczyn

Reputation: 37430

Try:

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

Upvotes: 2

Related Questions