Список создания запросов SQL Server не только TOP

Это основано на данных в SQLFiddle - я заметил, что там нет столбца «LASTNAME».

Кроме того, я все еще перевариваю, правильно это или нет, но, пожалуйста, посмотрите.

/* Find the next games for a user to rent */
/* You cant do it in one swoop because each customers choice will affect the next one */
/* and you have to insert the result into the rentals in between. */

SELECT FIRSTNAME, GAME_NAME, GAME_PLATFORM
FROM [Games]
JOIN (SELECT [Favourites_ID], [User_ID], [Game_name], [Game_platform],
  [Priority], [Priority_level], [DatePicked],
  RANK() OVER (PARTITION BY [User_ID] ORDER BY [Priority], [DatePicked]) AS Rank
 FROM Favourites) [Favourites]
  ON [Favourites].[Game_name] = [Games].[Name]
JOIN [Users]
  ON [Users].[User_ID] = [Favourites].[User_ID]
JOIN (SELECT [User_ID], MIN([RemainingRentable]) RemainingRentable FROM (
      /* Remaining rentable at a time */
      SELECT
        [Subscriptions].[Game_at_a_time] - (
          /* Number of games currently rented by the customer */
          SELECT COUNT(*) AS [CurraentlyRented]
          FROM [Rentals]
          WHERE [Rentals].[Date_returned] IS NULL
            AND [Users].[User_ID] = [Rentals].[User_ID]
        ) AS RemainingRentable,
      [Users].[User_ID]
      FROM [Users]
      JOIN [Subscriptions]
        ON [Users].[Subscription_ID] = [Subscriptions].[Subscription_ID]
      UNION
      /* Remaining rentable this month */
      SELECT
        [Subscriptions].[Max_games] - (
          /* Number of total games rented by the customer this month */
          SELECT COUNT(*) AS [RentedThisMonth]
          FROM [Rentals]
          WHERE MONTH([Rentals].[Date_rented]) = MONTH(GETDATE())
            AND YEAR([Rentals].[Date_rented]) = MONTH(GETDATE())
            AND [Users].[User_ID] = [Rentals].[User_ID]
        ) AS RemainingRentable,
      [Users].[User_ID]
      FROM [Users]
      JOIN [Subscriptions]
        ON [Users].[Subscription_ID] = [Subscriptions].[Subscription_ID] ) A
      GROUP BY [User_ID]
    ) Rentability 
  ON [Rentability].[User_ID] = [Users].[User_ID]
WHERE 
[Games].[Quantity] > (
    /* Number of currently rented copies */
    SELECT COUNT(*) FROM [Rentals]
    WHERE [Games].[Game_barcode] = [Rentals].[Game_barcode]
      AND [Rentals].[Date_returned] IS NULL
  )
AND Rank <= RemainingRentable
GROUP BY FIRSTNAME, GAME_NAME, GAME_PLATFORM