select most recent records of a student

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

SQL Server 2005 Forums

Transact-SQL (2005)

select most recent records of a student

Author

Topic

xcao
Starting Member

5 Posts

Posted - 2009-03-04 : 12:09:31

I have a web form when student fill the form, there are a small number of students clicked submit twice, then cause the duplicated records.

I will create a view that only pulls most recent records for the student. I thought I can group by studentfirst, studentlast, studentbirthdate, then choose the most recent datetime, but I cannot make it right? See the below query, how can I change it to work correctly?

SELECT EMID, DateSubmitted, ActivityID, StudentFirst, StudentLast, BirthDate, Gender, Grade, School, HomePhone, StreetAddress, City, Zipcode

FROM dbo.EmergencyCard
GROUP BY StudentFirst, StudentLast, BirthDate

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-04 : 12:17:34

Can you share Some sample data and expected output?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-04 : 12:45:15

[code]
SELECT EMID, DateSubmitted, ActivityID, StudentFirst, StudentLast, BirthDate, Gender, Grade, School, HomePhone, StreetAddress, City, Zipcode
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY StudentFirst, StudentLast, BirthDate ORDER BY datetime DESC) AS Seq,EMID, DateSubmitted, ActivityID, StudentFirst, StudentLast, BirthDate, Gender, Grade, School, HomePhone, StreetAddress, City, Zipcode
FROM dbo.EmergencyCard
)t
WHERE Seq=1
[/code]

xcao
Starting Member

5 Posts

Posted - 2009-03-04 : 13:51:57

visakh16 's query works very well.
But I would like to join an activity table,with activityID.
How can I add to this query?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources