Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Window Functions in MS SQL Server

Introduction
LEAD() and LAG()
FIRST_VALUE(), LAST_VALUE()
19. LAST_VALUE(x) – practice
Summary

Instruction

The query worked fine this time. Here's one thing you should remember: while FIRST_VALUE() works well with the default window frame, LAST_VALUE() needs an explicit definition of the right window frame to actually make sense.

Of course, you can order by one column and return the other:

SELECT
  Name,
  Budget,
  LAST_VALUE(Budget) OVER(ORDER BY OpenDate ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MostRecentBudget
FROM Website;

Here we show the budget of the most recent website (not the biggest budget).

How about trying to write your own example query? Are you ready?

Exercise

Show the statistics for WebsiteId = 1. For each row, show the Day, the number of impressions and the number of impressions on the day with the most users. Name the column LastImpressions.

Stuck? Here's a hint!

Use LAST_VALUE(Impressions) and sort by the number of users.