Every half-hour I query database to locate a publish that havn't released to write,each and every time I query by different category,if no posts within this category,change to next category.
The if check made by SQL
SELECT FROM post WHERE post.cat_ID='7' AND post_date > '$last_query_time_cat_ID_7' IF NO_POSTS_MATCH SELECT FROM post WHERE post.cat_ID='8' AND post_date>'$last_query_time_cat_ID_8' ...
pseudo explain:choose one publish of 1 category by its last query duration of this category,if no new publish of the category choose from another category by its last query time.
Can there be such SQL to get this done work?
You can do this by php to see if no posts return,make another sql query.This might make many queries.
Somebody show me the sql-pseudo-code of the problem?
Use Situation..WHEN...ELSE Statement.. Begin to see the link: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
Could seem like this:
SELECT p.post_ID, p.cat_ID, p.post_date FROM post p JOIN ( SELECT cat_ID ,max(query_time) AS last_query FROM post WHERE cat_ID >= 7 GROUP BY 1 ORDER BY 1) l USING (cat_ID) -- in some RDBMS you need ON l.cat_ID = p.cat_ID in the JOIN clause WHERE p.post_date > l.last_query ORDER BY p.cat_ID, p.post_date DESC LIMIT 1; -- In T-SQL you need to write "SELECT TOP 1" instead
First obtain the timestamp from the last query per category inside a subquery. We're only thinking about
cat_ID >= 7
Join this towards the base table and filter only rows having a later
Sort it by
cat_ID- you appear to wish littlest
cat_IDfirst. Secondary sort by
post_dateclimbing down, therefore the last publish comes first.
If you will find none in
cat_ID8 is going to be first etc. Voila.