SQL Select and Group by 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)

SQL Select and Group by help

Author

Topic

Tony.Valenti
Starting Member

4 Posts

Posted - 2005-02-25 : 18:05:10

I have a table that has these columns:
ID UserID ModuleID Core CreatedDate

What I am wanting to do is select the whole row where the created date per userid and moduleid is the maxiumum.

This psudo-sql looks like what I want, but I keep getting a group by error:

SELECT
*
FROM
Data_Table
GROUP BY
UserID,
ModuleID
HAVING
CreatedDate = Max(CreatedDate)

What should I do?

Thanks,
Tony Valenti

PW
Yak Posting Veteran

95 Posts

Posted - 2005-02-25 : 18:14:01

Use a virtual table to get the require date and join to it:

SELECT
d.*
FROM
Data_Table As d
INNER JOIN
(
SELECT UserID, ModuleID, Max(CreatedDate) As MaxDate
GROUP BY UserID, ModuleID
) vt
ON (vt.UserID = d.UserID AND
vt.ModuleID = d.ModuleID AND
vt.MaxDate = d.CreatedDate)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources