How to get the last entry for each day if the date is stored as an integer?

Question

MySQL

How to get the last entry for each day if the date is stored as an integer?

Answer 1

Something like this:

select * from (
  select 
  *,
  date(from_unixtime(created_at)) created_date,
  row_number() over (partition by created_date order by created_at desc) rn
  from tbl) row_numbers where rn = 1

or like this:

select tbl.* from tbl
join (
    select max(created_at) max_created_at
    from tbl
    group by date(from_unixtime(created_at))
) last_time on last_time.max_created_at = tbl.created_at;

Check SQL here