Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Subqueries in the SELECT Clause

Some systems allow subqueries in the SELECT statement where the subqueries act as SELECT list expressions. In the following query, you use SELECT clause correlated subqueries to find principal and second authors. Each subquery joins the outer table in the subquery WHERE clause, which specifies the authors included in the subquery results.

Transact-SQL, Adaptive Server Anywhere:

 select distinct title_id, (select au_id from titleauthors where au_ord = 1 and title_id = t.title_id) as first_author, (select au_id from titleauthors where au_ord = 2 and title_id = t.title_id) as second_authors, (select au_id from titleauthors where au_ord = 3 and title_id = t.title_id) as third_author from titleauthors t title_id first_author ...