LIMIT 1 (or COUNT) for each WHERE statement in 1 query? SQL (MySQL)

Hi,

I'm doing like a monthly news archive. On the website the links to the archive looks like this:
December
November
October
etc.

There's not always news in every month so I dont want links to those empty months.
Untill now I've been doing like this
1. a query to get the oldest news
2. a loop with queries with COUNT from each month, starting with the current month, going down to the month of the oldest news. The loop stops when it has found 12 months that has news, or when oldest month is reached. The queries have looked simple like this.
SELECT COUNT(news_id) FROM news WHERE (title LIKE '%something%') AND (news_pubdate >= 1164927600) AND (news_pubdate < 1167606000)
So the archive links could in theory look like this:
December
October 2005
November 2003

Well, this has turned out to be very slow, or at least CPU demanding. Also in the queries that take out the news I have changed the WHERE to use MATCH AGAINST instead of LIKE and it turned out to be 10+ times faster. So I would like to use that here too.

What I want is to do all this "monthly counting" in 1 query, something like this, (I know this structure is totally wrong), for like the latest 20 months. And then ignore any older.

SELECT (news_id) FROM news WHERE MATCH ... AGAINST ... (same for all)
(timestamp > xxx AND timestamp < yyyy) as month1 LIMIT 1
(timestamp > zzz AND timestamp < www) as month2 LIMIT 1
...
...

or something similar that uses count instead.
If you can't provide a solution, I'd be happy with any useful links too.

 

How about something like:

SELECT TOP (20) MONTH(datePosted) AS Month, YEAR(datePosted) AS Year, COUNT(*) AS numArticle
FROM tblNews
GROUP BY MONTH(datePosted), YEAR(datePosted)
ORDER BY Year DESC, Month DESC

The TOP(20) statement is for MSSQL. The MySQL equivalent is the Limit clause. Depending on the format of your date field, you may need to convert the month to the character equivalent.

The data that this statement returns would look like:
Month:Year:numArticle
12:2006:3
11:2006:5
8:2006:12
12:2005:23

This statement does not give you the news items themselves, but will give you what you need to populate the month list. The order by clause must be in that order to go chronologically backwards from the current date. The best part is that since you are looking at article submission dates, you do not have to filter months that have no articles (no records with those dates to count).