I'd like to learn how to use CTEs by example when working on my own project, by mutating it from individual calls into one clean call. I looked at the page (As I said in my previous email/note/post(?)) and just thought I'd put this question out there.
Given I have the schema posted here: http://pastebin.com/hA6weV4n Currently my application makes two queries to the database. One to get the list of projects via [ select ProjectID,Description from Projects order by Description ] It then goes through each record retrieved and then gets another query via [ select (select count(VideoID) from vViewedVideos where DateViewed is null and ProjectID=:P) Unwatched, (select count(VideoID) from vViewedVideos where ProjectID=:P) Videos ] where :P is the ProjectID. I then take the results of ProjectID, Description, Unwatched and Watched and format a string to put it into a listbox on the form. I want to change the order in which this listbox is populated based on a single query and the final sort order of [ order by Unwatched=0, Unwatched ] so that anything that has no unwatched videos available are at the bottom of list, and any videos that have something to watch are sorted ascending at the top of the list, 1 to whatever Maybe even add the flexibility later on down the line so that I can change the order based on [ order by Description ] or [ Unwatched=0, Description ] or whatever, just by changing the final Order By clause at runtime. >From what magic I've seen on the CTE page at sqlite.org, this should be easy to do, but I've not a clue where to start. If you'd like to look at the raw database, it is found here: https://dl.dropboxusercontent.com/u/1598459/sql/videos.db3 Any hints and thoughts would be appreciated!
