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 Administration

MAX function

Author

Topic

steve_joecool
Starting Member

21 Posts

Posted - 2014-06-09 : 19:35:33

I have a question(if it’s not too much to ask) about a specific query.

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 = '001i000000a1D3aAAE'
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 4 rows of information in return

Task ID AccountId name Last modified date Subject Alias
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

Is there any way I can filter that so I show ONLY the latest date on that list?

I thought the MAX function would do that, but obviously is not doing what I tried.

Any suggestions?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-09 : 19:37:30

select top 1 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 = '001i000000a1D3aAAE'
AND acc.OwnerId = tsk.LastModifiedById
group by tsk.AccountId, acc.Name, tsk.LastModifiedDate, tsk.Subject, usr.Alias, tsk.id
order by 4 desc

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

steve_joecool
Starting Member

21 Posts

Posted - 2014-06-09 : 19:40:58

quote:
Originally posted by tkizer

select top 1 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 = '001i000000a1D3aAAE'
AND acc.OwnerId = tsk.LastModifiedById
group by tsk.AccountId, acc.Name, tsk.LastModifiedDate, tsk.Subject, usr.Alias, tsk.id
order by 4 desc

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Thank you!

now, if I take the constraint of the accountID (meaning I will query all accounts). I need to show the last activity for EVERY account. I don't know if this will only show the first record.... am I right?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-10 : 11:50:04

You can use the ROW_NUMBER() function to achieve that.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources