declare @StagingTable TABLE
(
	Col1		varchar(64),
	Col2		varchar(64),
	Total_Size	numeric(10,0)
)

INSERT into @StagingTable (Col1,Col2,Total_Size)
select 'S1','C:',600 union all
select 'S1','C:',605 union all
select 'S1','C:',605 union all
select 'S1','C:',605 union all
select 'S1','C:',602 union all
select 'S1','C:',602 union all
select 'S1','C:',601 union all

select 'S2','C:',601 union all
select 'S2','C:',601 union all
select 'S2','C:',605 union all
select 'S2','C:',605 union all
select 'S2','C:',605 union all
select 'S2','C:',601 union all
select 'S2','C:',602 union all
select 'S2','C:',601 union all

select 'S3','D:',605 union all
select 'S3','D:',605 union all
select 'S3','D:',600 union all
select 'S3','D:',600 union all
select 'S3','D:',600 union all
select 'S3','D:',602 union all
select 'S3','D:',602 union all
select 'S3','D:',602 union all
select 'S3','D:',602 


	;WITH CTE ( Col1, Col2, [Total_Size], [Freq_TotalSize] )
	AS
	(
	SELECT	d.Col1 as Server_Name
			,Col2
			,Total_Size
			,COUNT(*) as Freq_TotalSize
			from @StagingTable d
			group by  
				 d.Col1
				,Col2
				,Total_Size
	)

	SELECT	m1.Col1, 
			m1.Col2,
			m1.ModeCount,
			m2.[Total_Size]
	FROM (
			SELECT Col1, Col2, 
					MAX( [Freq_TotalSize] ) as ModeCount
			FROM	CTE
			GROUP BY Col1, Col2
		) as m1
	JOIN CTE as m2
		ON	m2.Col1			= m1.Col1
		AND	m2.Col2				= m1.Col2
		AND m2.[Freq_TotalSize]	= m1.ModeCount
	ORDER BY m1.Col1, m1.Col2

SELECT Col1,Col2,Occur,Total_Size
FROM(
SELECT DISTINCT DENSE_RANK() OVER (PARTITION BY Col1,Col2 ORDER BY Occur DESC) AS Rnk,*
FROM
(
SELECT COUNT(1) OVER (PARTITION BY Col1,Col2,Total_Size) AS Occur,*
FROM @StagingTable
)t
)r
WHERE Rnk=1

SELECT		Col1,
		Col2,
		ModeCount,
		Total_Size
FROM		(
			SELECT	Col1,
				Col2,
				Total_Size,
				ModeCount,
				RANK() OVER (PARTITION BY Col1, Col2 ORDER BY ModeCount DESC) AS recID
			FROM	(
					SELECT		Col1,
							Col2,
							Total_Size,
							COUNT(*) AS ModeCount
					FROM		@StagingTable
					GROUP BY	Col1,
							Col2,
							Total_Size
				) AS d
		) AS d
WHERE		recID = 1
ORDER BY	Col1,
		Col2