Question: I want to group the key with count greater than 3, and the query will return the rest of the records also. I don't want to use Union All, is there any other way to do it?

ID   

1  

1  

1  

2  

3  

3  

4  

4  

4  

4  

Return  

1  

1  

1  

2  

3  

3  

4

Answer: 
You can use [ranking- and aggregate functions](http://msdn.microsoft.com/en-us/library/ms189461.aspx):

```
WITH CTE AS
(
  SELECT ID, 
         CNT = COUNT(*)     OVER (PARTITION BY ID),
         RN  = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID)
  FROM dbo.TableName
)
SELECT ID
FROM CTE
WHERE CNT <= 3 OR RN = 1

```

`[**Demo**](http://sqlfiddle.com/#!6/805b2/1/0)`
I'd do it like this

```
SELECT 
GroupedData.ID
FROM 
    (SELECT ID, CNT = COUNT(*)
    FROM dbo.TableName
    GROUP BY ID) GroupedData AS g
LEFT JOIN dbo.TableName AS  t
ON t.id = g.id and g.CNT<=3

```

This also allows you to add further columns which report details for the group or individual record as appropriate

```
SELECT 
g.ID, 
ISNULL(t.RecordName,'Grouped Records') as RecordName,
ISNULL(t.NumericField,g.NumericField)  as NumericField
FROM 
    (
     SELECT ID, CNT = COUNT(*), SUM(NumericField) as NumericField
     FROM dbo.TableName
     GROUP BY ID
    ) GroupedData AS g
LEFT JOIN dbo.TableName AS  t
ON t.id = g.id and g.CNT<=3

```