MySQL: Get most recent record satisfying certain conditions

Question

I was inspired by this post. But what I'm going to solve is more complex.

In the table below we have three columns, id,rating,created, call it test_table,

+----+--------+----------------------+
| id | rating | created              |
+----+--------+----------------------+
|  1 | NULL   | 2011-12-14 09:25:21  |
|  1 | 2      | 2011-12-14 09:26:21  |
|  1 | 1      | 2011-12-14 09:27:21  |
|  2 | NULL   | 2011-12-14 09:25:21  |
|  2 | 2      | 2011-12-14 09:26:21  |
|  2 | 3      | 2011-12-14 09:27:21  |
|  2 | NULL   | 2011-12-14 09:28:21  |
|  3 | NULL   | 2011-12-14 09:25:21  |
|  3 | NULL   | 2011-12-14 09:26:21  |
|  3 | NULL   | 2011-12-14 09:27:21  |
|  3 | NULL   | 2011-12-14 09:28:21  |
+----+--------+----------------------+

I want to write a query which selects the most recent rating but not null for every id. If all of the ratings are null for a specific id, we select the most recent rating. The desired result is as follows:

+----+--------+----------------------+
| id | rating | created              |
+----+--------+----------------------+
|  1 | 1      | 2011-12-14 09:27:21  |
|  2 | 3      | 2011-12-14 09:27:21  |
|  3 | NULL   | 2011-12-14 09:28:21  |
+----+--------+----------------------+

What is the primary key? – Paul Spiegel Mar 22 '18 at 19:57 — Paul Spiegel, Mar 22 '18 at 19:57

score 1 · Answer 1 · answered Mar 22 '18 at 19:22

1

The following gets the creation date:

select t.id,
       coalesce(max(case when rating is not null then creation_date end),
                creation_date
               ) as creation_date
from t
group by t.id;

You can then do this as:

select t.*
from t
where (id, creation_date) in (select t.id,
                                     coalesce(max(case when rating is not null then creation_date end),
                                     creation_date
                                    ) as creation_date
                              from t
                              group by t.id
                             );

answered Mar 22 '18 at 19:22

Gordon Linoff

1,242,037
58
646
786

I tested your solution, it seems that the result is not correct for `id = 3`. It gives the first row for `id = 3` instead of the last row. I think the problem is for `id = 3`, all the rating all null, thus max(null,...null) gives null and this gives the first row of `id = 3` by `coalesce` – Yueleng Mar 22 '18 at 20:33
@Yueleng . . . That is quite curious. This query is using `max()`, which should be the largest value. Obviously, if you want the earliest value, then just use `min()` instead. – Gordon Linoff Mar 26 '18 at 07:33

score 0 · Answer 2 · answered Mar 22 '18 at 19:19

select a.* from #test a join (select id, max(created) created
from #test
where rating is not null
group by id )b on a.id=b.id and a.created=b.created
union 
select a.* from #test a join 

(select id, max(created) created
from #test
where rating is  null
and id not in 

(select id from  (select id, max(created) created
from #test
where rating is not null
group by id )d

group by id)
group by id )b on a.id=b.id and a.created=b.created

score 0 · Accepted Answer · edited Mar 22 '18 at 20:12

One possible answer is this. Create a list of max(create) date per id and id having all NULL rating.

select t1.*
from myTable t1
join (
  select id, max(created) as created
  from myTable
  where rating is not NULL
  group by id 
   UNION ALL
  select id, max(created) as created
  from myTable t3
  where rating is NULL
  group by id 
  having count(*) = (select count(*) from myTable t4 where t4.id=t3.id)     
 ) t2
where t1.id=t2.id
and t1.created=t2.created
order by t1.id;

score 0 · Answer 4 · answered Mar 22 '18 at 19:42

This query should work:

select a.id, a.rating, b.m from test_table a
join (
    select id, max(created) as m from test_table 
    where rating is not null 
    group by id 
) b on b.id = a.id and b.m = a.created
union 
select a.id, a.rating, b.m from test_table a
join( 
    select id, max(created) as m from test_table a
    where not exists 
        (select 1 from test_table b where a.id = b.id and b.rating is not null)
    group by id 
)b on b.id = a.id and b.m = a.created

score 0 · Answer 5 · answered Mar 22 '18 at 20:02

You can get the created value in a correlated LIMIT 1 subquery:

select t.id, (
  select created
  from mytable t1
  where t1.id = t.id
  order by rating is null asc, created desc
  limit 1
) as created
from (select distinct id from mytable) t

If you also need the rating column, you will need to join the result with the table again:

select t.*
from (
  select t.id, (
    select created
    from mytable t1
    where t1.id = t.id
    order by rating is null asc, created desc
    limit 1
  ) as created
  from (select distinct id from mytable) t
) x
natural join mytable t

Demo: http://sqlfiddle.com/#!9/49e68c/8

MySQL: Get most recent record satisfying certain conditions

5 Answers5