SQLite design question

Question

Similar to a feed reader, I'm storing a bunch of articles, each pertaining to a source (feed) and each feed can belong to a category. What I'm trying to do is:

1. Retrieve the articles of the feeds that belong to a certain category.
2. Group the articles. One scenario would be by date(published_time), so that I have groups, for example: (12.04.09 - 3 articles, 17.04.09 - 9 articles, and so on)
3. Loop through each group and display each article. Pseudo-code:

foreach (Group group in results)
{
 print(group.Name);

 foreach (Article article in g.Articles)
 {
  print(article.Title);
  print(article.Content);
 }
}

I thought something simple like:

SELECT group_concat(item_id, '#') FROM items GROUP BY date(published_time)

would work. But then I'd have to split the resulting rows and loop through that (and there is no group_concat(*) function)

I'm confused as to how I would group(2) the results so that I can iterate through each one, preserving the group name. I thought that a SQL query returns ONE big table, and so, it seems to be impossible to accomplish this with just one query.

I reckon this is more of a DB design question, I'm also new to SQLite (SQL for that matter), so I ask you, gurus, how would one get this done efficiently?

Answer 1

SELECT Title, Content, date(published_time) AS Date
FROM items
ORDER BY date(published_time);

Pseudocode:

last = None
for r in results:
  if not last or r.Date != last.Date:
    print "Group", r.Date
  print r.Title, r.Content
  last = r