CODEWITHSUNDEEP
mysqlsql
Yueleng
Yueleng

Reputation: 105

MySQL: Get most recent record satisfying certain conditions

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  |
+----+--------+----------------------+

Upvotes: 0

Views: 115

Answers (5)

jose_bacoy
jose_bacoy

Reputation: 12684

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;

Upvotes: 0

Paul Spiegel
Paul Spiegel

Reputation: 31772

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

Upvotes: 0

isaace
isaace

Reputation: 3429

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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
                             );

Upvotes: 1

Daniel Marcus
Daniel Marcus

Reputation: 2686

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

Upvotes: 0

Related Questions