MySQL – get latest record when date and time are two fields

deadlockMySQLmysql-5.7

I have a DB in which date and time are two columns. I need to get the latest (by latest I mean the record with the highest time) row. This is used as a sub query to feed another query.

DB

id      date       time    currentOdometer    totalOdometer       vno
 1   2020-03-21    10:00      250                  3001          ABCD
 2   2020-03-21    09:59      249                  3000          ABCD          
 3   2020-03-21    10:01      251                  3002          ABCD
 4   2020-03-21    10:02      252                  3003          ABCD

My requirement is that when I get a new record of vno ABCD, i need to find the latest record and manipulate the data.

For instance, when i get a new record at 10:03, i need to find the latest record, ie 10:02 and the value.

My query was 

(
SELECT Y.currentOdometer+(3200-Y.totalOdometer) 
FROM tbl Y 
WHERE timeStamp(date,time) = 
(
  SELECT MAX(timeStamp(X.date,X.time)) 
  FROM tbl X WHERE vno= 'ABCD') AND vno= 'ABCD'
)

This query is part of an INSERT statement like this

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno) 
VALUES ('2020-03-21', '10:03',(SELECT Y.currentOdometer+(3200-Y.totalOdometer) 
FROM tbl Y 
WHERE timeStamp(date,time) = 
(
  SELECT MAX(timeStamp(X.date,X.time)) from tbl X 
  WHERE vno= 'ABCD') AND vno= 'ABCD'),3003,'ABCD'
)

When I run the query in this manner, i get the error the Subquery returns more than one row.

The i re framed the query with LIMIT 1 in each of the select statemtn and then there is a deadlock on the 

ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction
ER_LOCK_DEADLOCK
undefined

Best Answer

For questions like this, CREATE TABLE statements together with INSERT statements for sample data is truly useful. Even better is a Fiddle or similar. Right now, all I can do is give you some ideas, but I won't be able to test it.

Looking at your query:

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno) 
VALUES ('2020-03-21', '10:03',(SELECT Y.currentOdometer+(3200-Y.totalOdometer) 
FROM tbl Y 
WHERE timeStamp(date,time) = 
(
  SELECT MAX(timeStamp(X.date,X.time)) from tbl X 
  WHERE vno= 'ABCD') AND IMEI = 'ABCD'),3003,'ABCD'
)

There are probably some syntax errors ( un-balanced ")" for the sub-select, or at least they are in the wrong place ), but you should not use the VALUES clause together with a sub-select, better to re-write like:

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno) 
SELECT '2020-03-21', '10:03', Y.currentOdometer+(3200-Y.totalOdometer) 
FROM tbl Y 
WHERE timeStamp(date,time) = 
(
  SELECT MAX(timeStamp(X.date,X.time)) from tbl X 
  WHERE vno= 'ABCD') AND IMEI = 'ABCD'),3003,'ABCD'
)

As a side-note, avoid using reserved words as identifiers (date, time, timestamp).

The WHERE clause also looks wrong, I assume 3003 and 'ABCD' are values for totalOdometer, vno. Is this what you mean?

INSERT INTO tbl(date, time, currentOdometer, totalOdometer, vno) 
SELECT '2020-03-21', '10:03', Y.currentOdometer+(3200-Y.totalOdometer) 
      , 3003, 'ABCD'
FROM tbl Y 
WHERE timeStamp(date,time) = (
  SELECT MAX(timeStamp(X.date,X.time)) 
  from tbl X 
  WHERE vno= 'ABCD' 
    AND IMEI = 'ABCD'
);
Related Question