title: Cannot create CLUSTERED INDEX on a View due to LEFT JOIN or subquery
body: <p>I have created two options for a query used in a view which return the results I require. I need to rewrite either option so that it can be used in an <strong>Indexed View</strong>. Both fail 
  when creating a <strong>unique clustered index</strong> on the view. The first one fails due to the LEFT OUTER JOIN, and the second fails due to a subquery. I believe both will also fail due to 
  a self join.</p>
  
  <p>After finding <a href="http://msdn.microsoft.com/en-us/library/ms191432.aspx" rel="nofollow">Creating Indexed Views</a>, there is a large list of TSQL syntax elements that cannot be used. Among them: a derived table, UNION, EXCEPT, INTERSECT, subqueries, 
  Outer or self joins, TOP, ORDER BY, DISTINCT, MAX... </p>
  
  <p>The query should get the largest <code>CompanyID</code> for each unique <code>Company</code>. The <code>StatusName</code> in the <code>Statuses</code> table also needs to be displayed, and I'm only adding that in case it affects 
  the solution. It's currently an <code>INNER JOIN</code>, so it's not causing a problem with creating the index.</p>
  
  <p>Example for the <code>Companies</code> table, with all 3 columns being <code>INT</code>:</p>
  
  <pre><code>CompanyID Company Revision
  1         1       1
  2         1       2
  3         2       1
  4         2       2
  </code></pre>
  
  <p>The query should return:</p>
  
  <pre><code>CompanyID Company Revision
  2         1       2
  4         2       2
  </code></pre>
  
  <p>Here are the two options I've created:</p>
  
  <pre><code>SELECT t1.CompanyID, t1.Company, t1.Revision, Statuses.StatusName
  FROM dbo.Companies AS t1
  
  LEFT OUTER JOIN dbo.Companies AS t2
  ON t1.Company = t2.Company AND t1.CompanyID &lt; t2.CompanyID
  
  INNER JOIN dbo.Statuses
  ON dbo.Statuses.StatusID = t1.StatusID
  
  WHERE t2.Company IS NULL
  </code></pre>
  
  <p>And the other:</p>
  
  <pre><code>SELECT t1.CompanyID, t1.Company, t1.Revision, Statuses.StatusName
  FROM dbo.Companies AS t1
  
  INNER JOIN dbo.Statuses
  ON dbo.Statuses.StatusID = t1.StatusID
  
  WHERE t1.Company NOT IN (SELECT t2.Company from dbo.Companies AS t2 WHERE t1.CompanyID &lt; t2.CompanyID)
  </code></pre>
  
  <p>So, my question is, can either query be rewritten to be used in an Indexed View? </p>
  
  <p>I'm using MS SQL Server 2008 R2, and 2005.</p>