WITH CTE (SubscriptionEventId,	SubscriberId,	CommandType,	TIMESTAMPP,	ENDTIME)
AS
(
 SELECT 1,	'sbscr001',	'Activate',	'20140115',	'20130215'
   UNION ALL
 SELECT 2,	'sbscr001',	'HardCancel','20130215',	'20100108'
  UNION ALL
 SELECT 3,	'sbscr001'	,'Activate',	'20100108',	'20141026' 
  UNION ALL
 SELECT 4,'sbscr001','SoftCancel','20130107','20100108'
   UNION ALL
 SELECT 5,'sbscr001','HardCancel','20130215','20130107'
   UNION ALL
 SELECT 6,'sbscr002','Activate','20100108','20141026'
   UNION ALL
 SELECT 7,'sbscr002','Pause','20111008','20111008'
   UNION ALL
 SELECT 8,'sbscr003','Activate','20140525','20111008'      
   UNION ALL
 SELECT 9,'sbscr003','SoftCancel','20141026','20141026'  
),
R AS
(
  SELECT C.SubscriberId,C.CommandType,
  ROW_NUMBER()OVER(PARTITION BY C.SUBSCRIBERID ORDER BY C.SubscriptionEventId DESC)AS XCOL
FROM CTE AS C
)
SELECT FIN.*
FROM R AS FIN
WHERE FIN.XCOL=1;

/*
Требуется для каждого значения SubscriberId вывести CommandType строки с максимальным SubscriptionEventId.
*/