IF OBJECT_ID('TempDB..#activity','U') IS NOT NULL        
DROP TABLE #activity
--===== Create test table
CREATE TABLE #activity(
	Ref [nchar](10) NULL,
	Activity [nvarchar](max) NULL,
	activitytype [smallint] NULL
)
--===== Insert the test data into the test table
INSERT INTO #activity
	(Ref, Activity, activitytype)
SELECT '1         ','a','1' UNION ALL
SELECT '1         ','b','1' UNION ALL
SELECT '1         ','c','2' UNION ALL
SELECT '1         ','d','2' UNION ALL
SELECT '1         ','e','1' UNION ALL
SELECT '1         ','f','2' UNION ALL
SELECT '2         ','a','1' UNION ALL
SELECT '2         ','b','1' UNION ALL
SELECT '2         ','c','1' UNION ALL
SELECT '3         ','a','2'
--=====
--===== Select statement, where Ref =1 and the maximum of activitytype (an unknown but the max. is always required)
--=====
SELECT Ref
      ,Activity
      ,activitytype
	FROM #activity
	WHERE Ref = '1'
		AND activitytype =
			(SELECT max(activitytype) 
				FROM #activity
				WHERE Ref = '1')
--=====
--===== clean up
--=====
DROP TABLE #activity

-- MAX
SELECT Ref
      ,Activity
      ,activitytype
FROM #activity as ACT
WHERE Ref = '1'
	AND activitytype = (
		SELECT max(activitytype) 
		FROM #activity
		WHERE Ref = ACT.Ref
	)
	
-- TOP 1
SELECT Ref
      ,Activity
      ,activitytype
FROM #activity as ACT
WHERE Ref = '1'
	AND activitytype = (
		SELECT TOP 1 activitytype
		FROM #activity
		WHERE Ref = ACT.Ref
		ORDER BY activitytype DESC
	)