question

Create Schema and data CREATE TABLE Movie ( mID INT, title SYSNAME, year INT, director SYSNAME) --English: There is a movie with ID number mID, a title, a release year, and a director. CREATE TABLE Reviewer ( rID INT, name SYSNAME ) --English: The reviewer with ID number rID has a certain name. CREATE TABLE Rating ( rID INT, mID INT, stars INT, ratingDate DATETIME ) INSERT Movie SELECT 1,'Terminator 2',1991, 'James Cameron' UNION SELECT 2, 'Titanic',1997, 'James Cameron' UNION SELECT 3, 'Avatar',2010,'James Cameron' UNION SELECT 4,'Pretty Woman',1990,'Garry Marshall' UNION SELECT 5,'Pretty Woman',1990,'Garry Marshall' UNION SELECT 6,'Godzilla',1998,'Roland Emmerich' UNION SELECT 7,'Strangers on a Train ',1951,' Alfred Hitchcock ' INSERT Reviewer SELECT 1,'Gopi' UNION SELECT 2, 'Fred' UNION SELECT 3,'Ricky' UNION SELECT 4, 'Michael Tracy' INSERT Rating SELECT 1,7,4,'12/12/2010' UNION SELECT 1,1,4,'12/12/2010' UNION SELECT 1,2,4,'12/12/2010' UNION SELECT 1,6,2,'12/12/2010' UNION SELECT 2,7,4,'12/12/2010' UNION SELECT 2,1,5,'12/31/2010' UNION SELECT 3,1,5,'12/12/2010' UNION SELECT 3,4,5,'12/12/2010' UNION SELECT 3,6,1,'12/12/2010' UNION SELECT 1,7,5,'02/12/2011' UNION SELECT 2,1,2,'02/12/2011' UNION SELECT 3,4,3,'02/12/2011' UNION SELECT 4,6,1,'12/12/2010' UNION SELECT 4,1,3,'12/12/2010' UNION SELECT 4,1,4,'02/01/2011' Query WITH Rating_CTE(rID,mID,Stars,RatingDate,RowNumber) AS ( SELECT A.rID,A.mID,B.Stars,B.ratingDate,B.Row_Num FROM ( SELECT rID,mID FROM Rating GROUP BY rID,mID HAVING COUNT(1)>1 ) A INNER JOIN ( SELECT rID,mID,Stars,RatingDate,ROW_NUMBER() OVER (PARTITION BY rID,mID ORDER BY RatingDate DESC) AS Row_Num FROM Rating )B ON A.rID=B.rID AND A.mID=B.mID ) SELECT R.Name, M.Title from Rating_CTE A inner join Rating_CTE B on a.rId=B.rID and A.mID=B.mID and a.RowNumber<>B.RowNumber and a.Stars>b.stars and a.RowNumber =1 INNER JOIN Reviewer R on A.rID=R.rID INNER JOIN Movie M on A.mID=M.mID

Using the build script from @Gopi Muluka, (but changing all tables to be temporary objects for the example) I wouldnt choose to use a CTE and simply do it with a self join: SELECT [r].[stars] AS [First review], [r].[ratingDate] [First review date], [r3].[name] AS [Reviewer], [m].[title] AS [Film], [m].[year] AS [Year], [m].[director] AS [Director], [r2].[stars] AS [Second review], [r2].[ratingDate] AS [Second review date] FROM [#Rating] AS r INNER JOIN [#Reviewer] AS r3 ON [r].[rID] = [r3].[rID] INNER JOIN [#Movie] AS m ON [r].[mID] = [m].[mID] INNER JOIN [#Rating] AS r2 ON [r].[mID] = [r2].[mID] AND [r].[rID] = [r2].[rID] WHERE [r2].[ratingDate] > [r].[ratingDate] AND [r2].[stars] > [r].[stars] writing the query this way is about half the cost of using the CTE method.