Can I do that in the SQL statement?

Question

Let say I have a post table. But I want to query all today post. But if today post is less than 10 post, I will get back the yesterday post to query. If it is more than 10 posts, no need to query yesterday post....If SQL statement can't do it. Is this only achieve it by calling the post manually....? Thank you.

***The database is MySQL

Let me clarify the question in a typical example:

If today have 5 posts....ONLY. And yesterday have 10 posts.

return : 5 today posts, and 5 posts from yesterday

If today have 12 posts....ONLY.
And yesterday have 10 posts. 

return : 12 today posts.

If today have 10 posts....ONLY. And yesterday have 10 posts.

return : 10 today posts.

If today have 2 posts....ONLY. yesterday have 5 posts, and the day before yesterday 5posts. 

return : 2 today posts, 5 yesterday posts, 3 the day before yesterday posts.

Answer 1

You can try

select count(*) from post_table
where date = todays_date

and if the result is > 10 then

select * from post_table
where date = today's date

else

select * from post_table  
order by date desc
limit 10

Answer 2

Just another idea, a little bit shorter:

set @i = 0;
select *, @i := @i + 1
from post_table
where @i < 10 or date = today
order by date desc;

Not sure it is very effective.

Update: it is fast! I tested on the such sample:

create table a(i int primary key, d date not null, index idx(d)) 
set @i = 0;

insert into a(i, d)
select @i := @i + 1, adddate(curdate(), interval -(@i % 1000) day) 
from <100 records> a, <100 records> b, <100 records> c