Henrik,

Many thanks for your response.

I've played around with indexing strategies with my "Latest Edition" example using the dbo.Books table, and nothing is really helping.

The strange thing is that from an IO point of view, the ROW_NUMBER() solution does show a smaller scan and logical read count. However, the CPU time is very high.

For example:

SET STATISTICS IO ON

SET STATISTICS TIME ON

;WITH BookCTE (RowNumber, BookTitle, BookEdition, BookPublishDate, BookAuthor)

AS

(

SELECT

ROW_NUMBER()OVER (PARTITION BY BookTitle ORDER BY BookEdition DESC),

BookTitle, BookEdition, BookPublishDate, BookAuthor

FROM dbo.Books

)

SELECT BookTitle, BookEdition, BookPublishDate, BookAuthor

FROM BookCTE

WHERE RowNumber=1

ORDER BY BookTitle

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 28 ms.

(10 row(s) affected)

Table 'Books'. Scan count 1, logical reads 10132, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 18297 ms, elapsed time = 18295 ms.

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

from books 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;

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

(10 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Books'. Scan count 2, logical reads 20264, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 3609 ms, elapsed time = 3741 ms.

I think I may need Itzik to take a look at this. 😉

Will carry on with research, as time allows.

Regards,

Lawrence.