Need 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

SQL Server 2000 Forums

SQL Server Development (2000)

Need Query Help

Author

Topic

doublek321
Starting Member

6 Posts

Posted - 2005-08-03 : 22:58:38

TABLE: Topic

id groupid lastModified
1 1 2005-08-01 13:37:00.000
2 1 2005-08-03 06:00:00.000
3 1 2005-08-02 18:32:00.000
4 2 2005-07-29 12:00:00.000
5 2 2005-08-03 20:50:00.000

Above is the data in my table. I'd like to return the id column of the latest lastModified
for each groupid. So, in this case, I'd like to return ids 2, 5 (I don't mind if more columns
get returned) because id 2 contains the latest lastModified date for groupid 1 and id 5 contains
the latest lastModified date for groupid 2. The query below sort of works but it doesn't return
the column I'm interested in (id).

select t.groupid, max(t.lastModified)
from topic t
group by t.groupid

Any help would be greatly appreciated.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-08-03 : 23:33:16

try something like this:


SELECT t.[id], t.groupid, t.lastmodified 
FROM topic t INNER JOIN
(SELECT groupid as max_groupid, max(lastmodified) as max_last
 FROM topic
 GROUP BY groupid) t2
ON t.groupid = t2.max_groupid AND t.lastmodified = t2.max_last



id          groupid     lastmodified                                           
----------- ----------- ------------------------------------------------------ 
5           2           2005-08-03 20:50:00.000
2           1           2005-08-03 06:00:00.000

(2 row(s) affected)

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources