none
There is no Select TOP RRS feed

  • Question

  • I have one table that has commentid and commentdate.  I need to get the most recent record for each user based on commentid ( this is not an identity column) and commentdate.

    For instance for one commentid there are 13 posts by 3 people.  I just want to retrieve the the most recent post of each of those 3 people.

    Thanks
    Friday, May 29, 2009 5:50 PM

Answers

  • Assuming you have two tables (User and Comment) in a 1:many relationship, the solution is this:

    from u in Users
    select new
    {
       u.ID,
       MostRecentComment = u.Comments.OrderByDescending (c => c.CommentDate).FirstOrDefault()
    }

    You could alternatively take the maximum comment date and then match on that, but that approach would fail if two comments were made on the same date.

    If you have just a single table, then you'll need to first group on the UserID or UserName (whatever you're using to identify the user):

    from c in Comments
    group c on c.UserName into g
    select new
    {
       UserName = g.Key,
       LatestComment = g.OrderByDescending (c => c.CommentDate).First().Comment
    }

    Joe
    Write LINQ queries interactively - www.linqpad.net
    • Marked as answer by Mattaniah Monday, June 1, 2009 11:37 AM
    Saturday, May 30, 2009 3:08 AM
    Answerer

All replies

  • The sql way is a two step query:

    SELECT MAX( commentdate ) as lastdate, commentid INTO TABLE #temptable FROM table GROUP BY 2

    SELECT t.commentdate, t.commentid FROM table t INNER JOIN #temptable tt ON tt.commentid = t.commentid AND tt.lastdate = t.commentdate

    Friday, May 29, 2009 6:23 PM
  • var myComments =
      from u in Users
         let ld = u.Comments.Max(c=>c.CommentDate)
         from c in u.Comments
                        .Where(c => c.CommentDate == ld)
         select new  { 
             u.UserID,
             c.CommentDate,
             c.CommentID }
    BTW T-SQL way is a single step.

    select

     

     

    t1.UserId,t1.CommentId,t1.CommentDate
    from comments t1
    inner join
    (
    select
    UserID,
    max(commentDate) as commentDate
    from comments
    group by userId
    ) t2
    on t1.userId = t2.userId and t1.commentDate = t2.commentDate

    Friday, May 29, 2009 9:53 PM
  • Assuming you have two tables (User and Comment) in a 1:many relationship, the solution is this:

    from u in Users
    select new
    {
       u.ID,
       MostRecentComment = u.Comments.OrderByDescending (c => c.CommentDate).FirstOrDefault()
    }

    You could alternatively take the maximum comment date and then match on that, but that approach would fail if two comments were made on the same date.

    If you have just a single table, then you'll need to first group on the UserID or UserName (whatever you're using to identify the user):

    from c in Comments
    group c on c.UserName into g
    select new
    {
       UserName = g.Key,
       LatestComment = g.OrderByDescending (c => c.CommentDate).First().Comment
    }

    Joe
    Write LINQ queries interactively - www.linqpad.net
    • Marked as answer by Mattaniah Monday, June 1, 2009 11:37 AM
    Saturday, May 30, 2009 3:08 AM
    Answerer
  • I begin to use LIQN recently and i didn't know whether i do that by wright way. But you cat try it
    for example:
    var result = ( from p in dataContext.Table
      where p.SomeField == Something
      orderby p.Order descending
      select p).Take(1).SingleOrDefault();
    Saturday, May 30, 2009 10:32 AM
  • Hi Joe,
    Matching on date (which I think is a datetime in fact) would do IMHO. No human would be able to enter 2 comments on the same datetime and even if she could, both could be considered "last", no? Why would we try to select one of them as last and AFAIK there is no way to find out which one is the last then.

    PS: Of all the books I have read I loved your "C# 3.0 in a nutshell" (still reading through safari). Great stuff.
    Saturday, May 30, 2009 1:46 PM
  • Yes - if it included the time, it would be pretty difficult to enter two with the same value.

    Thanks for the positive feedback about the book!

    Joe


    Write LINQ queries interactively - www.linqpad.net
    Saturday, May 30, 2009 2:04 PM
    Answerer
  • To my knowledge I own all the albahari books as well.  I particulary enjoy the Pocket Reference. 
    بتكيد أنتم من اللبنان
    Monday, June 1, 2009 1:01 PM