CODEWITHSUNDEEP
mysqlsql
invalid_address
invalid_address

Reputation: 89

Select a row when a column changed

I have a table that stores when an object changes and when it was marked.

id  name   markedAt    updatedAt
1   X      2019-01-01  2019-01-01
2   x2     2019-01-01  2019-01-02
3   x2     null        2019-01-03
4   x2     2019-01-04  2019-01-04
5   x3     2019-01-04  2019-01-05

#1 - the object was marked and updated
#2 - the object was updated
#3 - was unmarked
#4 - was marked again
#5 - was updated

How do I write a query that retrieves the row when the object was marked for the last time. In this case, I want the row #4.

Upvotes: 1

Views: 63

Answers (2)

forpas
forpas

Reputation: 164214

You need first to get the maximum date where markedat = updatedat for each object with group by and then join to the main table:

select t.* 
from  tablename t
inner join  (
  select 
    name, 
    max(markedat) maxmarkedat
  from tablename 
  where 
    markedat = updatedat
  group by name 
) g 
on g.maxmarkedat = t.updatedat and g.maxmarkedat = t.markedAt and g.name = t.name

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

you could use a subquery for max marketAt in join 

    select m.* from  my_table m
    inner join  (
    select name, max(markedAt) max_mark
    from my_table
    group by name 
    ) t on t.markedAt = m.markedAt and t.name = m.name

this shoud return value for each name .. if you need just name = x2 add the proper where 

    select m.* from  my_table m
    inner join  (
    select name, max(markedAt) max_mark
    from my_table
    group by name 
    ) t on t.markedAt = m.markedAt and t.name = m.name 
    where name  = 'x2'

Upvotes: 1

Related Questions