title: SQL GROUP BY: Getting the most recently updated record for an indexed view
body: <p>I'm trying to do row versioning using an indexed view, grouping records by their key and timestamp, and taking the max(timestamp) record. This is fine, but the query I have used (see the view below) does a self join, meaning it can't be used in an indexed view, which i think will be essential to performance. Is there a way to rewrite the query so the view can be created successfully WITH SCHEMABINDING?</p>
  
  <p>I'm using 2005, but a 2008 only solution would be fine too.</p>
  
  <pre><code>IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Items]'))
      DROP VIEW [dbo].[Items]
  GO
  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ItemHistory]') AND type in (N'U'))
      DROP TABLE [dbo].[ItemHistory]
  GO
  CREATE TABLE [dbo].[ItemHistory](
      [Key] [nchar](10) NOT NULL,
      [Value] [int] NOT NULL,
      [TimeStamp] Timestamp NOT NULL,
      [LastUpdateBy] [varchar](50) NOT NULL CONSTRAINT [DF_ItemHistory_LastUpdateBy]  DEFAULT (SUSER_NAME()),
      [Deleted] BIT NOT NULL DEFAULT (0)
      CONSTRAINT [PK_ItemHistory] PRIMARY KEY CLUSTERED 
      (
          [Key] ASC,
          [TimeStamp] ASC
      ) ON [PRIMARY]
  )
  GO
  CREATE VIEW dbo.Items
  --WITH SCHEMABINDING --doesnt work with the query below :(
  AS
      SELECT ih.[key], ih.[Value] FROM ItemHistory ih
      INNER JOIN (
          SELECT [Key], Max([TimeStamp]) [TimeStamp]
          FROM ItemHistory
          GROUP BY [Key]
      ) ih2 ON ih.[key] = ih2.[key] AND ih.[TimeStamp] = ih2.[TimeStamp] AND Deleted = 0
  GO 
  INSERT INTO Items ([Key], [Value]) VALUES ('ItemA', 1)
  INSERT INTO Items ([Key], [Value]) VALUES ('ItemA', 2)
  INSERT INTO Items ([Key], [Value]) VALUES ('ItemA', 3)
  
  GO
  SELECT * FROM ItemHistory
  SELECT * FROM Items
  </code></pre>