sql query help

Desi Gal

15y2.1kSQL

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 ?

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Desi Gal15y ago
I am looking for the equivalent LINQ query
Suthish Nair15y ago
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 Gal15y ago
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 Nair15y ago
post your query
Desi Gal15y ago
@Suthist... I have already tried that
Suthish Nair15y ago
so change Active = 1 to appropriate status or number (like 0 --> Active, 1 --> In-Active).
Desi Gal15y ago
@Dorababu.. I am getting the rows where Active is false.
Guest User15y ago
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...

Dorababu Meka15y ago

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)
    }