Additional selection criteria
Select all the episode names of the first season of each series and sort them by name.
SELECT series_title, -- series_title is defined below in GROUP BY String::JoinFromList( -- calling a C++ UDF, -- see below AGGREGATE_LIST(title), -- an aggregate function that -- returns all the passed values as a list ", " -- String::JoinFromList concatenates -- items of a given list (the first argument) -- to a string using the separator (the second argument) ) AS episode_titles FROM episodes WHERE series_id IN (1,2) -- IN defines the set of values in the WHERE clause, -- to be included into the result. -- Syntax: -- test_expression (NOT) IN -- ( subquery | expression ` ,...n ` ) -- If the value of test_expression is equal -- to any value returned by subquery or is equal to -- any expression from the comma-separated list, -- the result value is TRUE. Otherwise, it's FALSE. -- using NOT IN negates the result of subquery -- or expression. -- Warning: using null values together with -- IN or NOT IN may lead to undesirable outcomes. AND season_id = 1 GROUP BY CASE -- CASE evaluates a list of conditions and -- returns one of multiple possible resulting -- expressions. CASE can be used in any -- statement or with any clause -- that supports a given statement. For example, you can use CASE in -- statements such as SELECT, UPDATE, and DELETE, -- and in clauses such as IN, WHERE, and ORDER BY. WHEN series_id = 1 THEN "IT Crowd" ELSE "Other series" END AS series_title -- GROUP BY can be performed on -- an arbitrary expression. -- The result is available in a SELECT -- via the alias specified with AS. ; COMMIT;