CODEWITHSUNDEEP
mysqlsql
Ron Melkhior
Ron Melkhior

Reputation: 475

Getting the last row with a certain column from each day in SQL

I have a table called logs that consists of a few columns.

id | state | result | timestamp

I need to all the rows with the state 'success-changed', but they appear at random, some days they don't and some they do twice. So in cases there is more than one per day I need to get the last one.

Can someone show me how to do it?

Thanks!

Upvotes: 3

Views: 124

Answers (2)

Adam Silenko
Adam Silenko

Reputation: 3108

Maybe you need add order by and group by to select...

select id , state , max(timestamp)
from logs 
where result = 'success-changed'
group by id , state
order by 3 desc

if you need id, state for last timestamp and for every day where result = 'success-changed', maybe this will be faster:

select id, state, timestamp
from logs l 
where result = 'success-changed'
and exists(
  select date(timestamp) 
  from logs 
  where result = 'success-changed'
  group by date(timestamp)
  having max(timestamp) = l.timestamp) 
order by 3 desc

you should have index on timestamp field.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Here is one method:

select l.*
from logs l
where l.result = 'success-changed' and
      l.timestamp = (select max(l2.timestamp)
                     from logs l2
                     where l2.result = 'success-changed' and
                           date(l2.timestamp) = date(l.timestamp)
                    );

This assumes that timestamp is stored as a datetime value.

Upvotes: 2

Related Questions