title: SQL Server 2008 - Finding duplicates using ROW_NUMBER
body: <p>i have the following SQL which works to find duplicates</p>
  
  <pre><code>SELECT  *
  FROM (SELECT
        id,
        ShipAddress,
        ShipZIPPostal,
        ROW_NUMBER() OVER (PARTITION BY shipaddress, shipzippostal ORDER BY shipaddress) ROWNUM
  FROM orders
  WHERE CONVERT(date, orderdate) = CONVERT(date, GETDATE())) x
  WHERE rownum &gt; 1
  </code></pre>
  
  <p>I would like to only see rows where, if the value of Rownum > 1 then i would like to see its corresponding row where rownum =1.</p>
  
  <p>So basically, if a row has duplicates, i want to see the original row and all its duplicates.</p>
  
  <p>If a row does not have duplicates, then i don't want to see it (it will have rownum = 1 )</p>
  
  <p>How would i do this please?</p>
  
  <p>cheers</p>