Question 74552 in Mysql Question posted in
The official MySQL documentation can be found here.

in MySQL, MAX() command returns an incorrect result

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.

Tags:

3

Answers


  1. 0

    Please do not include images in the question. Use text instead.

    You should tell from what set you want the max from (max of what?). You seem to have both date and max(date) in the query.

    GROUP BY statement should include all non-aggregate columns from the SELECT.

    Login or Signup to reply.
  2. 0

    Please execute below query to see whether the tag_id wise max date is in the top or not.

    SELECT tag_id, xpos, ypos, adsoyad, date FROM `lt_position` WHERE map_id="750" order BY tag_id date

    Please also execute this:

    SELECT tag_id, MAX(date) 
FROM `lt_position` 
WHERE map_id="750" 
GROUP BY tag_id
    Login or Signup to reply.
  3. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search