Display all records from a table where ROW_NUMBER>1 in SQL Server 2014

Question

I am trying to display all the records from a table where RN > 1

This is the code and the steps that I have followed

Create table #Data(id int,name Varchar(max))

Insert into #Data values
(1,'A'),
(2,'A'),
(3,'A'),
(4,'B'),
(5,'B'),
(1,'C')

Select *,ROW_NUMBER() over(partition by name order by id) as rn
into #temp
from #Data

--Fetching Subsequent records
select * from #Data
where name in 
(
   Select distinct name from #temp where rn>1
)

Output:

id  name
1   A
2   A
3   A
4   B
5   B

Can someone suggest a better approach that will not include intermediate temp table and a Sub query?

Answer 1

If I got it correct you want to count how many times each name occurs. And select names whose count > 1. Then no need to use row_number. Check this query

select
    id, name
from (
    select
        *, cn = count(*) over (partition by name)
    from
        #Data
) t
where
    cn > 1

Output

id  name
--------
1   A
2   A
3   A
4   B
5   B