I've lent a question and modified it for my very own reasons, however i can't stand carrying this out when I'm not entirely sure what it's doing. SQL paperwork are under descriptive about this clause. Here's what I lent and modified, do you know me what it's doing essentially?
(SELECT Id FROM ( SELECT Id ,RANK() OVER ( PARTITION BY DropStatusId ORDER BY StatusDate DESC) [Rank] FROM [dbo].[tblLHTrackingHistory] [TempHistory] WHERE [TempHistory].[DropStatusId] = [DropStatus].[Id] ) [TT1] WHERE [Rank] = 1 )
OVER clause means you are using statistics (versus say aggregates). Per the OVER documentation:
Determines the partitioning and ordering from the rowset prior to the connected window function is used.
Unlike aggregates, statistics do not require a
GROUP BY to become defined.
Great solutions to date.
A visual example helps:
DropStatusId StatusDate Rank 1 2010-02-19 1 < 1 2010-02-18 2 1 2010-02-17 3 2 2010-02-18 1 < 2 2010-02-15 2 2 2010-02-13 3 2 2010-02-12 4
The "partition" may be the records for any given DropStatusId.
Read this, it's pretty straight-forward. "Credit to Shaun Cruz"