SQL Server 2008 - ROWNUMBER OVER - filtering the result -

i have following sql works , returns products duplicate names , rownum column count of how many times name appears.

adding where rownum > 1 @ end gives me duplicates only.

select *      (select          id, productname,          row_number() on (partition productname                              order productname) rownum      products       group id, productname) result  

requirement

i need produce list of products if rownum column has value greater one, want see rows pertaining product grouped name column.

if rownum value product 1 only, , no value greater 1 (so no duplicate) don't want see row.

so example if "blue umbrella" appears 3 times, want see result product as:

id  name           rownum 35  blue umbrella   1 41  blue umbrella   2 90  blue umbrella   3 

how go achieving please?

change row_number on count(1) over , select count greater 1 , remove group by

select * (select id,productname,         count(1) over(partition productname order productname) rownum             products              ) result          rownum > 1