/*
	The following statements create the sample table, and insert
a few sample records for demonstration purposes.
*/
CREATE TABLE SampleData
(
	RecordId INT IDENTITY(1,1),
	TypeId INT,
	ActivityDate DATETIME,
	MessageText TEXT
)
GO

INSERT INTO SampleData
	(TypeId, ActivityDate, MessageText)
VALUES
	(1, '10/1/2008', 'Test Message (Max for Cat 1)')

INSERT INTO SampleData
	(TypeId, ActivityDate, MessageText)
VALUES
	(1, '9/28/2008', 'Test Message (Other for Cat 1)')

INSERT INTO SampleData
	(TypeId, ActivityDate, MessageText)
VALUES
	(2, '10/15/2008', 'Test Message (Max for Cat 2)')

GO

/*
	Use the CTE Expression to get the list of ranked
entries from the system, partition by to group by type
*/
WITH RankedData AS
(
	SELECT
		SD.RecordId,
		SD.TypeId,
		SD.ActivityDate,
		SD.MessageText,
    	Rank() OVER 
			(PARTITION BY SD.TypeId 
				ORDER BY SD.ActivityDate DESC) AS ActivityRank
    FROM SampleData SD
)
--Now select results
SELECT
    RankedData.RecordId,
	RankedData.TypeID,
	RankedData.ActivityDate,
	RankedData.MessageText
FROM RankedData
WHERE RankedData.ActivityRank = 1
ORDER BY TypeId

GO