Get the last inserted event

Question

Get the last inserted event

Below are my entries in the database table:

 id    cid     data_ref_id   event_type_id  event_handle     event_datetime

  1    235        1                162      Test description   2016-08-14
  2    235        1                162      Test1 description  2016-08-15
  3    235        1                162      Test2 description  2017-05-01
  4    235        1                162      Test3 description  2017-05-12

When showing event record for 2, it should show event 1 event_datetime as the previous record. For event 4 record, it must show event 3 event_datetime as the previous event record and so on ... Is there any sql way to get such a record?

+3

sql php mysql

Pranav Unde May 1 '17 at 4:33

source to share

3 answers

If I understand correctly, you can use a correlated subquery:

select t.*,
       (select max(t2.event_datetime)
        from t t2
        where t2.event_datetime < t.event_datetime
       ) as prev_datetime
from t;

0

Gordon linoff May 1 '17 at 4:36

source to share

SELECT event_type_id,MAX(event_datetime) FROM `table` WHERE 1;

This will select the last inserted event.

0

Rishabh Chaudhary May 1 '17 at 4:46

source to share

spencer7593 · Accepted Answer · 2017-05-01T05:36:08+0000

Assuming id

is a PRIMARY KEY or UNIQUE KEY in the "entries" table and assuming the "previous" event_datetime is value based id

(from line id = 1 for line id = 2, from line id = 3 for line id = 4) that something like this will return event_datetime from the "previous" line ...

SELECT e.id
     , e.event_datetime
     , ( SELECT p.event_datetime 
           FROM entries p
          WHERE p.id < e.id
          ORDER BY p.id DESC
          LIMIT 1
       ) AS prev_event_datetime
  FROM entries e
 ORDER BY e.id

If "previous" event_datetime

is value-based event_datetime

rather than value-based id

, then the correlated subquery can use the column event_datetime

instead id

.

SELECT e.id
     , e.event_datetime
     , ( SELECT p.event_datetime 
           FROM entries p
          WHERE p.event_datetime < e.event_datetime
          ORDER BY p.event_datetime DESC
          LIMIT 1
       ) AS prev_event_datetime
  FROM entries e
 ORDER BY e.id

Both options are presented here because the specification is not clear. We want the id = 1 line to be the "previous" for the id = 2 line, because the previous id

... or we get this line because the id = 1 line has the last one event_datetime

, which is earlier than event_datetime

the id = 2 line. This unclear.

Get the last inserted event

More articles: