sql query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql query help

Author

Topic

DesiGal
Starting Member

31 Posts

Posted - 2011-05-13 : 15:21:43

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 ?

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2011-05-13 : 15:35:15

Try


SELECT StudentHistoryId, StudentId, Grade, CreatedAt, ModifiedAt, ModifiedBy, Active
FROM TABLENAME TN
     INNER JOIN ( SELECT StudentId, MAX(ModifiedAt) AS ModifiedAt FROM TABLENAME GROUP BY StudentId) M
          ON TN.StudentId = M.StudentId AND TN.ModifiedAt = M.ModifiedAt

DesiGal
Starting Member

31 Posts

Posted - 2011-05-13 : 15:43:22

@tmaiden..That works...Thanks

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-13 : 22:43:54

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

What you did post looks like garbage. What is "student_history_id" ? Surely you know better than to use IDENTITY! Is "active" a silly BIT flag? The "CreatedAt". "ModifiedAt" and "ModifiedBy" columns look like audit data, which is illegal.

Be polite and try again with proper DDL.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

DesiGal
Starting Member

31 Posts

Posted - 2011-05-16 : 13:56:21

@CELKO
Can you be more specific. What do you mean by
The "CreatedAt". "ModifiedAt" and "ModifiedBy" columns look like audit data, which is illegal.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources