in MySQL, MAX() command returns an incorrect result

Question

I have a database which have columns with id, adsoyad, tag_id, map_id, xpos, ypos, and date.

This database receives data from each user every 15 minutes. Each user has a unique tag_id. The structure of the "date" column is in datetime format. When I run the code below;

SELECT tag_id, xpos, ypos, adsoyad, date, MAX(date) 
FROM `lt_position` 
WHERE map_id="750" 
GROUP BY tag_id

A column named Max(date) appears, the groupings are correct, but the Max(date) column lists the previous entry, not the last entry.

when I run this code, while listing, it lists the last registration date and the previous registration of the person.

What I want is to see the xpos and ypos data that each user submitted at the last registration time.

I tried some different code which I found in different solutions on the web and also from stackoverflow. But all of them give the same results to me.

I guessed my database has some problem. I tried to repair it a few times. I deleted and created it once. But the result is still the same.

Answer 1

What I want is to see the xpos and ypos data that each user submitted at the last registration time.

If so, that's a typical top-1-per-group problem.

If you are running MySQL 8.0, you can solve it with window functions:

select *
from (
    select p.*,
        rank() over(partition by tag_id order by date desc) rn
    from lt_position p
    where map_id = 750
) p
where rn = 1

In earlier versions, where window functions are not available, one alternative uses a correlated subquery:

select *
from lt_position p
where map_id = 750
  and l.date = (select max(p1.date) from lt_position p1 where p1.tag_id = t.tag_id)

For performance with the second query, consider an index on lt_position(tag_id, date).