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

WITH C AS ( SELECT ROW_NUMBER() OVER(PARTITION BY categoryid ORDER BY unitprice, productid) AS rownum, categoryid, productid, productname, unitprice FROM Production.Products ) SELECT categoryid, productid, productname, unitprice FROM C WHERE rownum <= 2;

From

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

Note

A common table expression may provide a clearer format for table expressions, breaking apart the inner and outer queries and specifying the outer query first.