How do I remove all but some records based on a threshold?

I have a table like this:

CREATE TABLE #TEMP(id int, name varchar(100))

INSERT INTO #TEMP VALUES(1, 'John')
INSERT INTO #TEMP VALUES(1, 'Adam')
INSERT INTO #TEMP VALUES(1, 'Robert')
INSERT INTO #TEMP VALUES(1, 'Copper')
INSERT INTO #TEMP VALUES(1, 'Jumbo')
INSERT INTO #TEMP VALUES(2, 'Jill')
INSERT INTO #TEMP VALUES(2, 'Rocky')
INSERT INTO #TEMP VALUES(2, 'Jack')
INSERT INTO #TEMP VALUES(2, 'Lisa')
INSERT INTO #TEMP VALUES(3, 'Amy')

SELECT *
FROM #TEMP


DROP TABLE #TEMP

I am trying to remove all but some records for those that have more than 3 names with the same id. Therefore, I am trying to get something like this:

id  name
1   Adam
1   Copper
1   John
2   Jill
2   Jack
2   Lisa
3   Amy

I am not understanding how to write this query. I have gotten to the extent of preserving one record but not a threshold of records:

;WITH FILTER AS
(
    SELECT id 
    FROM #TEMP
    GROUP BY id
    HAVING COUNT(id) >=3 
)
SELECT id, MAX(n开发者_运维知识库ame)
FROM #TEMP
WHERE id IN (SELECT * FROM FILTER)
GROUP BY id
UNION
SELECT id, name
FROM #TEMP
WHERE id NOT IN (SELECT * FROM FILTER)

Gives me:

1   Robert
2   Rocky
3   Amy

Any suggestions? Oh by the way, I don't care what records are preserved while merging.

You can do it using CTE

CREATE TABLE #TEMP(id int, name varchar(100))
INSERT INTO #TEMP VALUES(1, 'John')
INSERT INTO #TEMP VALUES(1, 'Adam')
INSERT INTO #TEMP VALUES(1, 'Robert')
INSERT INTO #TEMP VALUES(1, 'Copper')
INSERT INTO #TEMP VALUES(1, 'Jumbo')
INSERT INTO #TEMP VALUES(2, 'Jill')
INSERT INTO #TEMP VALUES(2, 'Rocky')
INSERT INTO #TEMP VALUES(2, 'Jack')
INSERT INTO #TEMP VALUES(2, 'Lisa')
INSERT INTO #TEMP VALUES(3, 'Amy')

SELECT *
FROM #TEMP;

WITH CTE(N) AS
(
 SELECT ROW_NUMBER() OVER(PARTITION BY id ORDER BY id)
 FROM #Temp
)
DELETE CTE WHERE N>3;

SELECT *
FROM #TEMP;

DROP TABLE #TEMP

I will change your select like this (not tested)

select name from #temp group by name having count(id) > 3

then you can implement your query in a delete statement using your select as a where clause

in inner query you can use row_number function over (partition by id) and then in outer query you have to give condition like below

select id,name from (
SELECT id,name, row_number() over (partition by id order by 1) count_id FROM #test
group by id, name ) 
where count_id <=3

If i got your question right, you need to get rows when id occurrence 3 or more times

select t1.name,t1.id from tbl1 t1 
inner join tbl1 t2 on t1.id = t2.id
group by t1.name, t1.id
having count(t1.id) > 2