Christian Buettner-167247 (1/19/2012)

---

WayneS (1/18/2012)

---

Comments posted to this topic are about the item <A HREF="/articles/SQL+Server+2012/76704/">The new Analytic functions in SQL Server 2012</A>

Great information, thanks for that.

To me it was not clear immediately, what the difference between the MAX Aggregate Window Function and LAST_VALUE(X) was. In the end MAX(x) OVER (PARTITION BY y) should return the same as FIRST_VALUE(X) OVER (PARTITION BY Y, ORDER BY X DESC)

But obviously, the MAX / MIN Aggregate Window Functions do not allow you to order your partition by a different column before applying the aggregate. (And it actually does not really make sense to impose a different order on MAX or MIN.)

So the whole point of FIRST_VALUE and LAST_VALUE seem to be:

FIRST_VALUE(X) OVER (PARTITION BY Y, ORDER BY SomeOtherColumn DESC)

Any other points I may have missed?