Query with join does not work correctly when there are trailing zeros in the field

Question

I have a change-log table that I only want to display the most recent entry for each document. But, when the document number has two trailing zeros, the query does not perform correctly. Any ideas how to fix it?

Example table:

number	changedatetime
1-050	2020-12-10
1-100	2020-12-10
1-123	2020-12-10
1-050	2020-12-15
1-100	2020-12-16
1-123	2020-12-17

Example query:

SELECT mdt.* 
FROM change_log mdt 
    LEFT JOIN change_log bdt ON mdt.number = bdt.number 
        AND mdt.changedatetime < bdt.changedatetime 
WHERE bdt.changedatetime IS NULL 
AND mdt.document='$doctype' 
ORDER BY mdt.number ASC

I expect to get a list of just the most recent like:

number	changedatetime
1-050	2020-12-15
1-100	2020-12-16
1-123	2020-12-17

But, I'm getting the most recent of all of them unless the document number has two trailing zeros:

number	changedatetime
1-050	2020-12-15
1-123	2020-12-17

The SQL database type for document number is "text" and changedatetime is "timestamp."

Might be worth a good long read https://stackoverflow.com/questions/153633/natural-sort-in-mysql — RiggsFolly, Jan 02 '21 at 19:57
It works fine (without the column `document` for which you did not post any data): https://www.db-fiddle.com/f/fP12xdLmNRgCwMdSh8yPxi/1 — forpas, Jan 02 '21 at 20:02
I don't see anything in the query related to the number that could affect the outcome this way. Are you sure that document and changedatetime match the criteria? — dbrumann, Jan 02 '21 at 20:06

score 0 · Accepted Answer · answered Jan 02 '21 at 22:38

Your question is incomplete, because you are referencing columns in the query that are not in the sample data. Presumably, you intend:

SELECT mdt.* 
FROM change_log mdt LEFT JOIN
     change_log bdt
     ON mdt.number = bdt.number AND
        mdt.document = bdt.document AND
        mdt.changedatetime < bdt.changedatetime 
WHERE bdt.changedatetime IS NULL AND
      mdt.document = ?
ORDER BY mdt.number ASC;

Note that I replaced the '$doctype' with ?, indicating that you should be passing in this value as a parameter, not munging the query string with the value.

I think the intention is much clearer using a correlated subquery:

select mdt.*
from change_log mdt
where mdt.changedatetime = (select max(bdt.changedatetime)
                            from change_log bdt
                            where mdt.number = bdt.number and
                                  mdt.document = bdt.document
                           ) and
      mdt.document = ?
order by mdt.number asc;

Query with join does not work correctly when there are trailing zeros in the field

1 Answers1