;with MyRank (col1)
as
(
	select 5 union all
	select 4 union all
	select 3 union all
	select 2 union all
	select 1 union all 
	select 1 union all 
	select 1
)

select col1, MAX(RowNum) as MaxRowNum
from
(
	select col1, ROW_NUMBER() over (order by col1 desc) as RowNum 
	from MyRank
) x 
group by col1
having Max(RowNum) <= 5
order by col1 desc

;with MyRank (col1)
as
(
	select 5 union all
	select 4 union all
	select 3 union all
	select 2 union all
	select 1 union all 
	select 1 union all 
	select 1
)
, MaxRank as
(
	select col1, ROW_NUMBER() over (order by col1 desc) as RowNum 
	from MyRank
)

select col1, MAX(RowNum) as MaxRowNum
from MaxRank
group by col1
having Max(RowNum) <= 5
order by col1 desc

;with MyRank (col1)
as
(
	select 5 union all
	select 4 union all
	select 3 union all
	select 2 union all
	select 1 union all 
	select 1 union all 
	select 1
)
SELECT TOP 5 col1
FROM MyRank
GROUP BY col1
HAVING COUNT(*) = 1
ORDER BY col1 

;with MyRank (col1)
as
(
	select 6 union all
	select 5 union all
	select 4 union all
	select 3 union all
	select 2 union all
	select 2 union all 
	select 1
), MyRank2 
as
(
SELECT TOP 5 col1, COUNT(*) AS RCount
FROM MyRank
GROUP BY col1
)
SELECT col1
FROM MyRank2
WHERE RCount = 1

;with MyRank (col1)
as
(
	select 6 union all
	select 5 union all
	select 4 union all
	select 3 union all
	select 2 union all
	select 2 union all 
	select 1
), MyRank2 
as
(
SELECT TOP 5 col1, COUNT(*) AS RCount
FROM MyRank
GROUP BY col1
)
SELECT col1
FROM MyRank2
WHERE RCount = 1

;with MyRank (col1)
as
(
	select 6 union all
	select 5 union all
	select 4 union all
	select 3 union all
	select 2 union all
	select 2 union all 
	select 1
), MyRank2 
as
(
	SELECT TOP 5 col1, COUNT(*) AS RCount
	FROM MyRank
	GROUP BY col1
	order by col1 desc --Need this order by to ensure which top 5
)

SELECT col1
FROM MyRank2
WHERE RCount = 1

Sean Lange (8/29/2012)

---

Luis Cazares (8/29/2012)

---

I tested my last solution and won't give the exact results.
Try this instead:

;with MyRank (col1)
as
(
	select 6 union all
	select 5 union all
	select 4 union all
	select 3 union all
	select 2 union all
	select 2 union all 
	select 1
), MyRank2 
as
(
SELECT TOP 5 col1, COUNT(*) AS RCount
FROM MyRank
GROUP BY col1
)
SELECT col1
FROM MyRank2
WHERE RCount = 1

Still not quite right. You have a TOP but not Order By. Which top 5 will it get?

Adding the order by seems to get it though.

;with MyRank (col1)
as
(
	select 6 union all
	select 5 union all
	select 4 union all
	select 3 union all
	select 2 union all
	select 2 union all 
	select 1
), MyRank2 
as
(
	SELECT TOP 5 col1, COUNT(*) AS RCount
	FROM MyRank
	GROUP BY col1
	order by col1 desc --Need this order by to ensure which top 5
)

SELECT col1
FROM MyRank2
WHERE RCount = 1

This seems to have a slightly simpler execution plan than mine too. We have certainly proven there is more than 1 way to do this. I am sure somebody will come along with another one within a couple hours.

;with MyRank (col1)
as
(
	select 6 union all
	select 5 union all
	select 5 union all
	select 4 union all
	select 3 union all
	select 2 union all 
	select 1
), MyRank2 
as
(
	SELECT TOP 5 col1, COUNT(*) AS RCount
	FROM MyRank
	GROUP BY col1
	order by col1 desc --Need this order by to ensure which top 5
)

SELECT col1
FROM MyRank2
WHERE RCount = 1