Additional selection criteria
Select all the episode names of the first season of each series and sort them by name.
Note
We assume that you already created tables in step Creating a table and populated them with data in step Adding data to a table.
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;