0

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
6
  • What version of MySQL are you using? Please always include it in your questions - edit the tags! Also, could you please provide your table structures as DDL and your data as DML. Help us to help you! p.s. as @Lennart says, do not use SQL keywords!
    – Vérace
    Mar 21 '20 at 9:33
  • Noted ... MySQL keywords have been changed to new names.
    – nXn
    Mar 21 '20 at 9:43
  • Could you please include all of the your query/(ies) - the field IMEI isn't there for example. The query as written can't run, so any solution that we may propose can't be tested!
    – Vérace
    Mar 21 '20 at 9:55
  • The field is vno not IMEI. That was a typo error.
    – nXn
    Mar 21 '20 at 10:28
  • There are more fields to the table. My issue is that there is almost 50 to 80 times the query is being executed in a minute and the data being inserted to the db. I get the data to be inserted once or twice after which there is an DEADLOCK error. There are almost 25 fields
    – nXn
    Mar 21 '20 at 10:34
1

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'
);
1

The only solution I've found for the similar project is a little denormalization of the database.

Along the table containing every point for all the vnos you have to create the auxiliary table of the same structure except there is no ID column and vno column is a primary key. On each insert into the main table you have to INSERT ... ON DUPLICATE KEY UPDATE into the auxiliary table where only the last received values are stored for each vno. Sure you have to verify is newly received date and time are bigger than existing ones. Some trackers can send NMEA in an arbitrary order in some cases.

This approach has a little overhead (two inserts instead of single one) but allows to avoid time-consuming grouping for MAX() calculations. Indeed it's better to wrap both inserts and validations into transaction.

0

You have found a problem with splitting date and time. But it is not unsolvable.

I discuss how to iterate through a compound key here: http://mysql.rjweb.org/doc.php/deletebig#iterating_through_a_compound_key

Instead of using MAX, use ORDER BY and LIMIT 1.

If you are using MySQL 8 or MariaDB 10.2, there is also the OVER windowing function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.