CTEs are generally poor performing. We have had to rewrite several for performance reasons. Your example breaks down when you have a table with a lot of rows and takes over an order of magnitude longer to execute. You should not be doing any performance evaluation a such a small table. The CTE is the worst performing of the 3 methods I know of. You do not mention the third which is joining a subquery back to the table.

select b.BookTitle, b.BookEdition, b.BookPublishDate, b.BookAuthor

frombooks b

inner join

(select booktitle, max(bookedition) as bookedition from books group by booktitle)q

on b.booktitle = q.booktitle and b.bookedition = q.bookedition;

I reinserted your data back into the table 17 times and updated the edition with the identity column:

Derived table:

Table 'Books'. Scan count 17, logical reads 15461

390 Milliseconds

Correlated subquery:

Table 'Books'. Scan count 17, logical reads 15461

400 Milliseconds

CTE:

Table 'Books'. Scan count 17, logical reads 21203

8103 Milliseconds!!!!!!!!!!