title: SQL query to fetch products with most top 1 most recent price change
body: <p>I'm using SQL Server 2005.</p>
  
  <p>Let's say I have a table for products and another table for prices so that I can track price changes over time. I need a query that fetches distinct products (easy part) plus each product's most recent price and the date it changed. </p>
  
  <p>Products Table:</p>
  
  <pre><code>CREATE TABLE [dbo].[Products](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [Name] [varchar](50) NOT NULL,
      [Price] [money] NOT NULL,
   CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
  ( [ID] ASC )
  ) ON [PRIMARY]
  GO
  
  INSERT INTO Products (Name, Price) VALUES ('Hat', 10);
  INSERT INTO Products (Name, Price) VALUES ('Shirt', 15);
  INSERT INTO Products (Name, Price) VALUES ('Pants', 20);
  INSERT INTO Products (Name, Price) VALUES ('Coat', 25);
  INSERT INTO Products (Name, Price) VALUES ('Shoes', 30);
  </code></pre>
  
  <p>PriceChanges Table:</p>
  
  <pre><code>CREATE TABLE [dbo].[PriceChanges](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [ProductId] [int] NOT NULL,
      [Price] [money] NOT NULL,
      [PriceChanged] [datetime] NOT NULL,
   CONSTRAINT [PK_PriceChanges] PRIMARY KEY CLUSTERED 
  ( [ID] ASC )
  ) ON [PRIMARY]
  GO
  
  INSERT INTO PriceChanges (ProductId, Price, PriceChanged) VALUES (1, 9.65, '1/1/2010');
  INSERT INTO PriceChanges (ProductId, Price, PriceChanged) VALUES (1, 10.10, '1/2/2010');
  INSERT INTO PriceChanges (ProductId, Price, PriceChanged) VALUES (1, 11.50, '1/3/2010');
  INSERT INTO PriceChanges (ProductId, Price, PriceChanged) VALUES (2, 15.50, '1/4/2010');
  INSERT INTO PriceChanges (ProductId, Price, PriceChanged) VALUES (2, 15.65, '1/5/2010');
  INSERT INTO PriceChanges (ProductId, Price, PriceChanged) VALUES (3, 19.95, '1/6/2010');
  INSERT INTO PriceChanges (ProductId, Price, PriceChanged) VALUES (4, 24.95, '1/7/2010');
  </code></pre>
  
  <p>Here's a query that returns too much:</p>
  
  <pre><code>SELECT     
      p.ID ProductId, 
      p.Name, 
      COALESCE(c.Price, p.Price) Price, 
      c.PriceChanged
  FROM dbo.Products AS p LEFT JOIN 
          dbo.PriceChanges AS c ON c.ProductId = p.ID
  </code></pre>
  
  <p>Returns: </p>
  
  <pre><code>1   Hat     9.65    2010-01-01 00:00:00.000
  1   Hat     10.10   2010-01-02 00:00:00.000
  1   Hat     11.50   2010-01-03 00:00:00.000
  2   Shirt   15.50   2010-01-04 00:00:00.000
  2   Shirt   15.65   2010-01-05 00:00:00.000
  3   Pants   19.95   2010-01-06 00:00:00.000
  4   Coat    24.95   2010-01-07 00:00:00.000
  5   Shoes   30.00   NULL
  </code></pre>
  
  <p>What I need it to return is:</p>
  
  <pre><code>1   Hat     11.50   2010-01-03 00:00:00.000
  2   Shirt   15.65   2010-01-05 00:00:00.000
  3   Pants   19.95   2010-01-06 00:00:00.000
  4   Coat    24.95   2010-01-07 00:00:00.000
  5   Shoes   30.00   NULL
  </code></pre>
  
  <p>This query works, but it's got two nested selects and it will make DBA's around the world weep:</p>
  
  <pre><code>SELECT     
      p.ID ProductId, 
      p.Name, 
      COALESCE((SELECT TOP 1 Price FROM dbo.PriceChanges WHERE ProductId = p.ID ORDER BY PriceChanged DESC), p.Price) Price, 
      (SELECT TOP 1 PriceChanged FROM dbo.PriceChanges WHERE ProductId = p.ID ORDER BY PriceChanged DESC) PriceChanged
  FROM dbo.Products AS p 
  </code></pre>
  
  <p>What's a better way to do this?</p>