How can I write an SQL version of this LINQ query?

As shown at: How to li开发者_如何学Cmit an SQL query to return at most one of something?

I have the following tables:

Platforms

• PlatformID
• Name

Products

• ProductID
• Name

Releases

• ID
• ProductID
• PlatformID
• Version
• ReleaseDate

The releases table stores each release of a software product for a particular platform. I want to write a query to return the latest release of each product for each platform in the Releases table. I.E. If I release product X version 2.0 on platforms A, B and C, there will be 3 records in the Releases table:

• one for product X version 2.0 on platform A released on 2010-11-17
• one for product X version 2.0 on platform B released on 2010-11-17
• one for product X version 2.0 on platform C released on 2010-11-17

So, given a product ID, how do I obtain this information? I tried using GROUP BY like the LINQ solution I used but apparently I can't translate LINQ to SQL as well as .NET can.

Pedantic:

Select
  PR.Name,
  PL.Name,
  REL.Version,
  REL.ReleaseDate
From
  Releases As REL
  Inner Join Products As PR
    On REL.ProductID = PR.ProductID
  Inner Join Platforms As PL
    On REL.PlatformID = PL.PlatformID
Where
  REL.ID = (Select Top 1 Latest.ID
            From Releases As Latest
            Where Latest.PlatformID = REL.PlatformID
              And Latest.ProductID = REL.ProductID
            Order By Latest.ReleaseDate Desc)

Should also work (assuming you never decrease versions):

Select
  PR.Name,
  PL.Name,
  Max(REL.Version),
  Max(REL.ReleaseDate)
From
  Releases As REL
  Inner Join Products As PR
    On REL.ProductID = PR.ProductID
  Inner Join Platforms As PL
    On REL.PlatformID = PL.PlatformID
Group By
  PR.Name,
  PL.Name

Did you know LINQ's DataContext class has a Log property?

Since you already have a LINQ query, why not peek behind the curtain? :-)