[thelist] SQLite group by query

[erik mattheis]

I need to pull messages from a SQLite table ordering them in the style of
Facebook posts: Original posts are sorted by date descending, replies are
sorted by date ascending.

My table:

sql.text= "CREATE TABLE IF NOT EXISTS messages(" +
  "postId INTEGER PRIMARY KEY AUTOINCREMENT, " +
  "GUID TEXT, " +
  "parentGUID TEXT, " +
  "siteId TEXT, " +
  "threadDate DATETIME, " +
  "datePosted DATETIME) ";

Not to confuse you before you think about it, but this seemed to be working
while threadDate and postDate were all the same date:

sql.text = "SELECT a.* " +
  "FROM messages AS a " +
  "LEFT JOIN messages AS b ON a.GUID = b.parentGUID " +
  "WHERE a.siteId = @siteId " +
  "GROUP BY a.GUID, a.parentGUID, a.threadDate " +
  "ORDER BY threadDate DESC, " +
  "CASE  " +
  "   WHEN a.parentGUID IS NULL THEN a.GUID " +
  "ELSE a.parentGUID " +
  "END  " +
  ", a.datePosted"

-- 
Erik Mattheis

http://www.flickr.com/gelk