Advanced SQL

WINDOW FUNCTIONS

// add row number
SELECT *, ROW_NUMBER() OVER () AS row_number
    FROM enrolled

OVER: specify how to group together tuples when computing the window function

PARTITION BY: specify group

SELECT cid, sid, ROW_NUMBER() OVER (PARTITION BY cid) AS row_number
    FROM enrolled
    ORDER BY cid

We can also include an ORDER BY in the window grouping to sort entries in each group

SELECT *, ROW_NUMBER() OVER (ORDER BY cid) 
    FROM enrolled
    ORDER BY cid

Find the student with the highest grade for each course.

SELECT * FROM (
    SELECT *,
        RANK() OVER (PARTITION BY cid
                        ORDER BY grade ASC)
        AS RANK 
    FROM enrolled) AS ranking
    WHERE ranking.rank = 1

COMMON TABLE EXPRESSIONS

Bind output columns to names before the AS keyword

    WITH cteName (col1, col2) AS (SELECT 1,2)
    SELECT col1 + col2 FROM cteName

CTE Recursion

WITH RECURSIVE cteSource (counter) AS (
    (SELECT 1)
    UNION ALL
    (SELECT counter +1 FROM cteSource 
    WHERE counter < 10)
)
SELECT * FROM cteSource