How to limit an SQL query to return at most one of something?

Question

I have a table in my database representing releases of software; the relevant columns of the schema being as follows:

ProductID   int
PlatformID  tinyint
Date        datetime

All are not null, and there is also a unique key assigned to the first two columns.

I would like to build a query (using LINQ) which returns the latest release (e.g. Date) for each platform (PlatformID), but no more than one record per platform (PlatformID), and is sorted by Date descending.

How can I do this?

---

EDIT: Here is the solution I used:

var product = ...;

var latestReleases = product.Releases
    .GroupBy(p => p.PlatformID)
    .Select(group => group.OrderByDescending(p => p.Date).FirstOrDefault())
    .OrderByDescending(release => release.Date)

for clarity...

foreach (var release in latestReleases)
{
    Console.WriteLine("The latest release of MyProduct for {0} is {1}.",
        release.PlatformID, // of course this would be a string in real code ;)
        release.Version);
}

Answer 1

I think the following code does what you’re asking for:

var latestProducts = db.Releases
    .GroupBy(p => p.PlatformID)
    .Select(grp => new {
        Platform = grp.Key,
        Latest = grp.OrderByDescending(p => p.Date).FirstOrDefault()
    })
    .OrderByDescending(entry => entry.Latest.Date);

foreach (var entry in latestProducts)
{
    Console.WriteLine("The latest product for platform {0} is {1}.",
        entry.Platform,
        entry.Latest.ProductID);
}

Here’s how it works:

• First, take all the products
• Group them into groups by common PlatformID
• Within each group, sort the products in that group by date descending and pick the first (that’ll be the one with the latest date)
• For each group, create an object containing the PlatformID and the platform’s latest Product
• Sort this list of objects by date descending.

Answer 2

If you are using LINQ to Entities, LINQ to SQL, etc, then just use First() or FirstOrDefault(), they will be smart enough to limit the query to 1. If you prefer to be more explicit, you can add Take(1) to your query.

Answer 3

                from plat in 
                (
                    from p in Products
                    group p by p.PlatformID
                )

                let latestRelease = 
                (
                    from p in plat
                    orderby p.Date descending
                    select p
                ).FirstOrDefault()

                where latestRelease != null

                select new 
                {
                    PlatformID = plat.Key,
                    LatestProductID = latestRelease.ProductID,
                    LatestDate = latestRelease.Date
                }

Answer 4

One line code:

IEnumerable<Product> products = AllProducts.GroupBy(p => p.PlatformID, 
    (pid, source) => source.Where(p => p.PlatformID == pid).OrderByDescending(p => p.Date).FirstOrDefault());