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