How to Blog Monthly Archive???

[eluser]dinhtrung[/eluser]
Hi all,
I want to write a monthly archive for Blog page.
The blogs table has a field named `archive`, which will store the year and month part of creation date.
For example, a blog with creation date 2010-01-22 will have archive field 2010-01.
In MySQL, to retrieve the number of blog post per month, I would do:

Code:

SELECT COUNT(*) AS `num_rows`, `archive` FROM `blogs` GROUP BY `archive`;

The above command will produce something like this:

Code:

+----------+---------+
| num_rows | archive |
+----------+---------+
|        5 | 2009-12 |
|        1 | 2010-01 |
+----------+---------+

The question is, how to do this with ActiveRecords?
And is there similar methods in other db driver, than MySQL?
Please help me.

[eluser]rogierb[/eluser]
Hi

Code:

$this->select('count(*) as num_rows, archive',false)
$this->db->get('blogs')
$qry = $this->db->group_by('archive')

I only use MySQL but I guess this would work for most db drivers