O'Reilly logo
  • Dustin Waybright thinks this is interesting:

SELECT categoryid, productid, productname, unitprice FROM Production.Products AS P1 WHERE unitprice = (SELECT MIN(unitprice) FROM Production.Products AS P2 WHERE P2.categoryid = P1.categoryid);

From

Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012

Note

Correlated subqueries take a subset of the outer query reference table and are joined to it, thus not independent.