Using ROW_NUMBER() to Get the Last Event for Each Category

Josh,

So the scenario is you have a table with a list of events like this:

CREATE TABLE [dbo].[Events](
   [EventDate] [datetime] NULL,
   [Category] [varchar](10) NULL,
   [EventDescription] [varchar](60) NULL
)  

You only want the latest record for each Category.  One way to accomplish this is the ROW_NUMBER() function...

WITH orderedEvents
AS(
   SELECT
       ROW_NUMBER() OVER (PARTITION BY Category ORDER BY EventDate DESC) AS CategoryOrder,
       EventDate,
       Category,
       EventDescription
   FROM
       dbo.Events
)SELECT
   *FROM
   orderedEvents
WHERE
   CategoryOrder = 1  

The ROW_NUMBER() function returns a bigint representing the row of the record within a group (aka partition) in the designated order.  If the PARTITION BY clause is excluded the row numbers will be calculated on the entire data set.  If it is included, the numbering will start over at each Category in this case.  The ORDER BY clause tells the function which way the row numbering should begin.

In this statement, the function is saying split this data set by Category, order each subset by EventDate descending and return the order of each row in the subsets.

The select statement is then wrapped with a Common Table Expression (CTE), and only the first record of each Category is returned:

More info:
MSDN: ROW_NUMBER()