Group - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Group

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-03-30 : 00:51:40

I have a table look like:


p_id  date           test_name   
1001  01/02/05          A         
1001  01/02/05          B
1001  02/28/05          A
1002  02/01/05          B
1002  02/01/05          C
1003  01/12/05          C
1003  01/30/05          A
1003  02/15/05          C

I would like to return a list of people who took the same test in a different date and it's maximum date.


The result should look like:
P_id     date         test
1001     02/28/05     A
1003     02/15/05     C

andy8979
Starting Member

36 Posts

Posted - 2005-03-30 : 05:13:22

Hi, Try this query

Select distinct pid, testname, Max(dt) As Dt, Count(pid)
from @Table
Group By pid, testname
having count(pid) > 1
order by pid, testname

It will work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources