max date - 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 2005 Forums

Transact-SQL (2005)

max date

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-15 : 07:06:46

Hello,
Looking at the data below, can you let me know how to get the result I require please?
I simply would like to get one record for each entry name which has the max date.
The RESULT to be retrieved is at the bottom.
Thanks

declare @tblMain table
(
ID int identity(1, 1) primary key clustered,
FileDate smalldatetime,
[Name] varchar(20),
ProcessID tinyint
)

insert into @tblMain (FileDate, [Name], ProcessID)
select '01/01/2010', 'Johnson', 2
union
select '01/02/2010', 'Johnson', 2
union
select '01/03/2010', 'Johnson', 2
union
select '01/05/2010', 'Johnson', 3
UNION
select '01/01/2010', 'Paterson', 2
union
select '01/02/2010', 'Paterson', 2
union
select '01/03/2010', 'Paterson', 2
union
select '01/06/2010', 'Paterson', 1

select FileDate, [Name], ProcessID from @tblMain

--RESULT
'01/05/2010', 'Johnson', 3
'01/06/2010', 'Paterson', 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-15 : 07:16:12

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

stepson
Aged Yak Warrior

545 Posts

Posted - 2010-01-15 : 07:20:04

with CTE (data1,name1,ex1)
AS (
select '01/01/2010' as data1, 'Johnson' as name1, 2 as ex
union
select '01/02/2010', 'Johnson', 2
union
select '01/03/2010', 'Johnson', 2
union
select '01/05/2010', 'Johnson', 3
UNION
select '01/01/2010', 'Paterson', 2
union
select '01/02/2010', 'Paterson', 2
union
select '01/03/2010', 'Paterson', 2
union
select '01/06/2010', 'Paterson', 1)

select A.datamax,A.name1,B.ex1
from (
select max(data1) datamax,name1 as name1
from CTE
group by name1 ) A
inner join
(
select data1,name1,ex1
from CTE
)B on A.datamax=B.data1 and A.name1=B.name1

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-15 : 09:50:43

Thank you

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 10:39:07

You can also make use of ranking functions in 2005...like..

select FileDate, [Name], ProcessID  from 
(
select row_number() over(partition by [Name] order by FileDate desc) as seq,* 
from @tblMain
) a
where a.seq = 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-18 : 01:59:11

quote:
Originally posted by vijayisonly

You can also make use of ranking functions in 2005...like..
select FileDate, [Name], ProcessID from
(
select row_number() over(partition by [Name] order by FileDate desc) as seq,*
from @tblMain
) a
where a.seq = 1

See the link I posted. One of the methods is to use ranking functions

Madhivanan

Failing to plan is Planning to fail

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2010-01-19 : 14:57:53

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources