title: Filter result set of a CTE based on a computed column
body: <p>I am trying to find the columns which appeared in more than one table in a given database. I have succeeded in coming up a result set which is giving column name, table name and additional rank column and a flag (0 or 1) for more than one occurrence.</p>
  <p>I am just wondering if its possible to filter this CTE based on last column</p>
  <pre><code>;with CTE as
  (
      SELECT c.name AS ColName, t.name AS TableName, ROW_NUMBER() over (partition by c.name order by c.name) as NumberofOcuurance
      FROM sys.columns c
      JOIN sys.tables t 
      ON c.object_id = t.object_id    
  )    
  select *, 
      case
      when    LEAD(NumberofOcuurance,1) over (order by colName) = NumberofOcuurance then  0
      when    LEAD(NumberofOcuurance,1) over (order by colName) &lt;&gt; NumberofOcuurance then  1
      End as Morethan1    
  from CTE 
  </code></pre>