sql query help

Question

I have a history table for Students in SQL Server 2008.

StudentHistoryId, StudentId, Grade, CreatedAt, ModifiedAt, ModifiedBy, Active

How do I write a tsql query to get the latest modified row for all the active students ?

John Penn · Answer

You can do this by joining to a subquery: SELECT history.* FROM history INNER JOIN (SELECT h.StudentId MAX(h.ModifiedAt) AS LastModified FROM history AS h WHERE Active = 1 GROUP BY h.StudentId) Recents ON Recents.StudentId = history.StudentId AND Recents.LastModified = history.ModifiedAt I haven't tested this so it may need some tweaking...

Desi Gal · Answer

I am looking for the equivalent LINQ query

Suthish Nair · Answer

Am assuming Active students as 1. Select * From Students_History SH Where SH.Active = 1 and SH.ModifiedAt in ( select MAX(SH1.modifiedat)from Students_History SH1 where SH1.Active = 1 group by SH1.RuleId)

Desi Gal · Answer

This is the SQL query which gives me the results I want. I am trying to convert this to LINQ Select * From Students_History SH Where SH.Active = 1 and SH.ModifiedAt in ( select MAX(SH1.modifiedat)from Students_History SH1 group by SH1.RuleId)

Suthish Nair · Answer

post your query

Desi Gal · Answer

@Suthist... I have already tried that

Suthish Nair · Answer

so change Active = 1 to appropriate status or number (like 0 --> Active 1 --> In-Active).

Desi Gal · Answer

@Dorababu.. I am getting the rows where Active is false.

Dorababu Meka · Answer

Try this var q = from h in history where h . Active group h by h . StudentId into g select new { StudentID = g . Key  LatestModified = g . Max ( x => x . ModifiedAt ) }