title: Can SQLite subqueries contain ORDER BY?
body: <p>I have the following SQL that selects discussion forum threads with accompanying info about the first/last posts, number of posts and the user who started it:</p>
  
  <pre><code>SELECT forum_threads.*, 
         users.displayname as cc_username, 
         coalesce(count(forum_posts.id),0) AS cc_postCount,
         (select id from forum_posts where threadId=forum_threads.id order by tsCreate asc limit 1) as cc_firstPostId,
         (select id from forum_posts where threadId=forum_threads.id order by tsCreate desc limit 1) as cc_lastPostId,
         (select tsCreate from forum_posts where threadId=forum_threads.id order by tsCreate desc limit 1) as cc_tsLatest
  FROM forum_threads
  LEFT JOIN forum_posts ON forum_posts.threadId = forum_threads.id
  LEFT JOIN users ON users.id = forum_threads.userId
  GROUP BY forum_threads.id
  ORDER BY forum_posts.tsCreate DESC
  </code></pre>
  
  <p>It seems to work fine, but I just read on <a href="https://www.tutorialspoint.com/sqlite/sqlite_sub_queries.htm" rel="nofollow noreferrer">Tutorialspoint</a> that SQLite subqueries cannot contain <code>ORDER BY</code>. Mine do, and they work. </p>
  
  <p>How come they work? Was I just lucky, and should I structure my query in some other way?</p>