AND ml.mid=b.rid
AND p.cid=ml.mid
ORDER BY manifestTime DESC;
</pre></nowiki>

Not yet sure how it behaves across branches, and it certainly doesn't track renames.

<h2>Latest Checkin Info for a Given Branch</h2>

This can probably be done more simply, but here's an approach which uses a CTE to select a list of branch names:

<nowiki><pre>
WITH branch(name) AS (
  SELECT 'dave'
  UNION ALL
  SELECT 'trunk'
),
latestByBranch(name, rid, uuid, ts) AS(
     SELECT branch.name, event.objid, blob.uuid, datetime(max(event.mtime))
     FROM tag, tagxref, event, branch, blob
     WHERE tag.tagname='sym-' || branch.name
     AND tagxref.tagid=tag.tagid
     AND tagxref.tagtype>0
     AND event.objid=tagxref.rid
     AND blob.rid=event.objid
     AND event.type GLOB 'ci'
     GROUP BY branch.name
     -- ORDER BY event.mtime DESC
     -- LIMIT 2
)
SELECT * FROM latestByBranch
ORDER BY ts DESC
;
</pre></nowiki>
<h2>Recursion Example</h2>

See also: [http://www.sqlite.org/draft/lang_with.html]

The following is adapted from a sqlite mailing list post by Petite Abeille on 20140203, not directly applicable to fossil but which is an interesting example nonetheless and which can certainly be used as a model for generating fossil-related data:

<nowiki><pre>