Max aggregate 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

SQL Server 2008 Forums

Transact-SQL (2008)

Max aggregate function

Author

Topic

reneelacks
Starting Member

1 Post

Posted - 2009-03-31 : 23:49:06

I'm trying to pull the maximum DatePosted for each Vendor...

VendorName VendorCode OperatorID DatePosted

ABC Rental 0001 1234 1/1/2008
Nottingham 0002 1234 2/3/2008
ABC Rental 0001 5678 8/9/2008
Fox Chevrolet 0003 5678 10/12/2008
Dolls Unity 0004 1234 3/4/2009
Chesapeake 0005 5678 11/6/2008
Dolls Unity 0004 1234 5/5/2008

My desired result:

ABC Rental 0001 1234 8/9/2008
Nottingham 0002 1234 2/3/2008
Fox Chevrolet 0003 5678 10/12/2008
Dolls Unity 0004 1234 5/5/2008
Chesapeake 0005 5678 11/6/2008

Any help would be most appreciated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-01 : 01:15:02

select VendorName, VendorCode, OperatorID, DatePosted
from (
select VendorName, VendorCode, OperatorID, DatePosted, row_number() over (partition by vendorid order by dateposted desc) as recid
from table1) as f
where recid = 1

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources