Have you seen this tutorial? https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/
-- Bill Drago Staff Engineer L3 Narda-MITEQ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / William.Drago at L-3COM.com > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski > Sent: Monday, March 07, 2016 9:47 PM > To: General Discussion of SQLite Database > Subject: [sqlite] CTE for a noob > > 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! > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments.
