Help with MAX function - 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

General SQL Server Forums

New to SQL Server Programming

Help with MAX function

Author

Topic

steve_joecool
Starting Member

21 Posts

Posted - 2014-06-10 : 10:48:40

Hello,

I am having an issue with the following query, and was wondering if anyone could give a helping hand.

Here's my query:

select tsk.id as "Task ID",
tsk.AccountId,
acc.name,
MAX(CONVERT(DATE, tsk.LastModifiedDate, 131))as "Last modified date",
tsk.Subject,
usr.Alias
from task as tsk
join Account as acc on acc.Id = tsk.AccountId
join users as usr on tsk.LastModifiedById = usr.Id
where tsk.AccountId in ('001i000000a1D3aAAE','001i000000MP0eTAAT')
--note that I just added another account for example purposes, ultimately that line needs to go away)
AND acc.OwnerId = tsk.LastModifiedById
group by tsk.AccountId, acc.Name, tsk.LastModifiedDate, tsk.Subject, usr.Alias, tsk.id
order by 4 desc

I get 6 rows in return.

Task ID AccountId name Last modified date Subject Alias
00Ti000000gmfGqEAI 001i000000MP0eTAAT MCCAIN FOODS USA, INC - OTHELLO 6/2/2014 email:FW: Scale Receiver Vibrator Motor BMcCa
00Ti000000gZGmeEAG 001i000000MP0eTAAT MCCAIN FOODS USA, INC - OTHELLO 5/30/2014 Call BMcCa
00Ti000000eZTbAEAW 001i000000a1D3aAAE HERSHEY FOODS CORPORATION - WEST HERSHEY 5/15/2014 Call bnest
00Ti000000QAoe6EAD 001i000000a1D3aAAE HERSHEY FOODS CORPORATION - WEST HERSHEY 1/13/2014 Email: Line 3 Project bnest
00Ti000000QAp31EAD 001i000000a1D3aAAE HERSHEY FOODS CORPORATION - WEST HERSHEY 1/13/2014 Email: RE: Perrigo bnest
00Ti000000QApTEEA1 001i000000a1D3aAAE HERSHEY FOODS CORPORATION - WEST HERSHEY 1/13/2014 Email: RE: Line 3 Project bnest

However; I am only interested in the latest activity per account (should only be 2 records in this report which are underlined). I thought the MAX function would do that. But I am not getting what I need.

Any suggestions?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-06-10 : 11:00:53

MAX function would return one row each unique combination of the columns in the group by clause. So in your example, it would return one row for each unique combination of Name, LastModifiedDAte, Subject, Alias, and Id. Remove the LastModifiedDate from the group by clause and see if you get what you are looking for.

If even that returns more rows than you expect, then see if the subject or alias, or one of the other columns is different in the duplicate rows.

steve_joecool
Starting Member

21 Posts

Posted - 2014-06-10 : 11:48:54

AAAAHHHHH I See.... Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources