Max Returning more than one record

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

Max Returning more than one record

Author

Topic

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-11-08 : 15:23:06

when this query is run it returns the max value for each of the activity types eg. phone calls, emails etc.
what i want to achieve is for it to return only one record. whichever is more recent. but it only has to be either a phone call or an email.

SELECT regardingobjectidname, MAX(actualend) AS Last_Contacted_On, activitytypecodename, owneridname
FROM FilteredActivityPointer AS A
WHERE (statecodename = 'completed') AND (activitytypecodename IN (@activitytypes))
GROUP BY regardingobjectidname, activitytypecodename, owneridname

Melvin Felicien
IT Manager
DCG Properties Limited

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 15:32:42

SELECT TOP 1 regardingobjectidname, actualend, activitytypecodename, owneridname
FROM FilteredActivityPointer
WHERE statecodename = 'completed' AND activitytypecodename = @activitytypes
order by actualend desc

E 12°55'05.25"
N 56°04'39.16"

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-11-08 : 15:51:07

This one doesnt work as it only returns 1 record but i need it to return one record for each "regardingobjectidname"
Table
regardingobjectid | actualend | activitytype |Owneridname
melvin felicien 10/10/07 email Matthew
melvin felicien 12/10/07 phone matthew

i need it to only return record number two because its the most recent. but this has to be run against a databas with thousands of regardingobjectid's

Please help

Compnetsyslc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 15:56:37

Are you using SQL Server 2005 or SQL Server 2000?

E 12°55'05.25"
N 56°04'39.16"

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-11-08 : 15:59:39

2005

Compnetsyslc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 16:01:28

[code]SELECT regardingobjectid,
actualend,
activitytype,
Owneridname
from (
SELECT regardingobjectid,
actualend,
activitytype,
Owneridname,
row_number() Over (Partition by regardingobjectid order by actualend desc) as recid
FROM FilteredActivityPointer
WHERE statecodename = 'completed'
AND activitytypecodename = @activitytypes
) as d
where recid = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 17:23:56

Well? Did it work?

E 12°55'05.25"
N 56°04'39.16"

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-11-08 : 17:42:30

Yes thank you. you are a star. it did work.

Compnetsyslc

SwePeso
Patron Saint of Lost Yaks

30421 Posts