Max date need only 1 record returned

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 2008 Forums

Transact-SQL (2008)

Max date need only 1 record returned

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2013-12-10 : 17:23:00

Running this:
select agent_login_id, location, supervisor_name, title, MAX(start_date) as Start_date1
from Agents
group by agent_login_id, location, supervisor_name, title

It outputs
agent_login_id location supervisor_name title Start_date1
User1 USA Manager1 Title1 06/04/12
User2 AUS Manager1 Title1 07/01/13
User3 USA Manager1 Title1 10/29/10
User3 USA Manager2 Title1 10/29/10
User3 USA Manager3 Title1 10/29/10
User3 USA Manager4 Title1 10/29/10
User3 USA Manager5 Title1 10/29/10
User3 USA Manager6 Title1 10/29/10
User4 USA Manager1 Title1 10/10/08
User4 USA Manager1 Title2 10/10/08
User4 USA Manager1 Title3 10/10/08
User4 USA Manager2 Title3 10/10/12

The indented output is:
agent_login_id location supervisor_name title Start_date1
User1 USA Manager1 Title1 06/04/12
User2 AUS Manager1 Title1 07/01/13
User3 USA Manager6 Title1 10/29/10
User4 USA Manager2 Title3 10/10/12

So it will take the LAST DATE, if the dates are all of the same then it outputs only 1 person per line.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-11 : 07:36:51

[code]
SELECT agent_login_id, location, supervisor_name, title, Start_date1

FROM (SELECT ROW_NUMBER() OVER (PARTITION BY agent_login_id,location ORDER BY Start_date1 DESC,
supervisor_name DESC,title DESC) AS RN,*
FROM Table
)t
WHERE RN =1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources