CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2005t-sql
joshlrogers
joshlrogers

Reputation: 1134

Could someone please explain OVER

I have borrowed a query and adapted it for my own purposes, but I don't like doing this when I am not entirely sure what it is doing. SQL docs are less than descriptive on this clause. Here is what I borrowed and modified, can you tell me what it is doing basically?

(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
)

Upvotes: 6

Views: 218

Answers (3)

mechanical_meat
mechanical_meat

Reputation: 169284

Great answers so far.
Sometimes 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" is the records for a given DropStatusId.

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332541

The OVER clause means you're using analytics (vs say aggregates). Per the OVER documentation:

Determines the partitioning and ordering of the rowset before the associated window function is applied.

Unlike aggregates, analytics don't require a GROUP BY to be defined.

Upvotes: 5

dugas
dugas

Reputation: 12443

Read this, it is pretty straight-forward. "Credit to Jeff Smith"

Upvotes: 2

Related Questions