CODEWITHSUNDEEP
mysqlsqldatabase
user4747724
user4747724

Reputation:

getting weird value back when trying to find the max row of a table based on date

I'm trying to do something I thought was relatively simple. Get the last entered value of a user. I have a table which tracks all their entries called plan_activities_logs. I used this statement to get all the activity regarding a single user:

SELECT created_at as last_active, plan_id, plan_value 
from plan_activity_logs where plan_id IN (select id from plans where tile_id = 30);

and it gives me back a table that looks like this:enter image description here

but when I try to do something like this:

SELECT MAX(created_at) as last_active, plan_id, plan_value from plan_activity_logs where plan_id IN (select id from plans where tile_id = 30);

I get this: enter image description here

while the date and id are correct the plan value is the wrong value. Any idea what I'm doing wrong?

Upvotes: 0

Views: 44

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37367

Try this query:

select @rn := 1;
select created_at as last_active, 
       plan_id, 
       plan_value
from (
    select created_at as last_active, 
           plan_id, 
           plan_value,
           @rn := @rn + 1 rn
    from plan_activity_logs
    where plan_id IN (select id from plans where tile_id = 30)
    order by created_at desc
) a where rn = 1;

Upvotes: 0

krokodilko
krokodilko

Reputation: 36107

Any idea what I'm doing wrong?

you are not doing anything wrong. According to the manual

If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want.

In other words, this behavior is in accordance with the specification. The query returns a random non-deterministic value because you are using MySql nonstandard extension to the group by query.

Upvotes: 1

Shyamala Gopalakrishnan
Shyamala Gopalakrishnan

Reputation: 533

This should work

SELECT created_at as last_active, plan_id, plan_value from plan_activity_logs where plan_id IN (select id from plans where tile_id = 30) order by created_at desc limit 1;

Upvotes: 0

Related Questions