Search code examples
sql-serverwebmatrixrazor-2

Fetch only one of each entry based on date

Im currently working with SQL Compact 4 and Razor and im trying to fetch only one of each entry based on latest date

id Name Number LastDate 1 Joe 1111 2014-01-01 2 Sam 2222 2014-01-02 3 Joe 1111 2014-04-11 4 Sam 2222 2014-04-12 5 Lee 3333 2014-04-12

I'm trying to write the data out to a webgrid but i can't find the correct SQL statement to only load id 3, 4 and 5 since they are the last updated entries.

Solution

  • Try with 

    SELECT id, Name, Number, LastDate FROM yourTable t1 INNER JOIN
    (SELECT Number, MAX(LastDate) AS MaxDate FROM yourTable GROUP BY Number) t2
ON t1.Number = t2.Number AND t1.LastDate = t2.MaxDate