Additional selection criteria

    Select all the episode names of the first season of each series and sort them by name.


    We assume that you already created tables in step Creating a table and populated them with data in step Adding data to a table.

        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
        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.