Partitioned Group Query

Paul Nielsen — Thu, 26 Jun 2008 18:03:00 GMT

In the SSWUG Virtual Conference this morning, in the chat room following my Kill the Cursor session, Boris asked, "How can I find the last five events for every device, without a cursor?"

Here's the script, Boris. Essentially it uses the Over clause with a Partition by in a subquery to segment by device and number each row. The outer query then has a where clause to restrict the results to the last five.

Boris, I'm interested in the performance gains you see with the set-based query over the cursor. Once you test this will you please post your findings? thanks,

USE TempDB

CREATE TABLE Events (

        EventID INT IDENTITY NOT NULL PRIMARY KEY,

        Device CHAR(2),

        EventTime DATETIME,

        Data CHAR(10)

  )

GO

INSERT Events( Device, EventTime, Data)

    VALUES ('a', GETDATE(), 'asd')

INSERT Events( Device, EventTime, Data)

    VALUES ('b', GETDATE(), 'qwe')

INSERT Events( Device, EventTime, Data)

    VALUES ('c', GETDATE(), 'sdf')

  -- waitfor delay '00:00:00.100'

GO 1000

INSERT Events( Device, EventTime, Data)

    VALUES ('a', GETDATE(), 'last five')

INSERT Events( Device, EventTime, Data)

    VALUES ('b', GETDATE(), 'last five')

INSERT Events( Device, EventTime, Data)

    VALUES ('c', GETDATE(), 'last five')

      WAITFOR delay '00:00:00.100'

GO 5

SELECT *

  FROM (

  SELECT EventID, Device, EventTime,

        Row_Number() OVER(partition BY device

      ORDER BY eventtime DESC) AS RowNum

    FROM Events) sq

  WHERE RowNum <= 5

Paul Nielsen : over partition sswug virtual conference T-SQL

Partitioned Group Query