How to get the Maximum 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)

How to get the Maximum date

Author

Topic

kkiranvr
Yak Posting Veteran

54 Posts

Posted - 2009-04-28 : 14:24:27

Hi all,
Here is my i/p table looks like
Number date1
125 5/8/2007
125 5/9/2008(Maximum date for number)
125 6/8/2006
125 6/9/2007
130 15/8/2007
130 25/10/2008(Maximum date for number)
130 16/8/2006
130 26/9/2007

now i am expecting o/p table as
Number date1 Final_Date
125 5/8/2007 5/9/2008
125 5/9/2008 5/9/2008
125 6/8/2006 5/9/2008
125 6/9/2007 5/9/2008
130 15/8/2007 25/10/2008
130 25/10/2008 25/10/2008
130 16/8/2006 25/10/2008
130 26/9/2007 25/10/2008

I am having nearly one million records in that table. and i want to get the maximum date to be filled in Final_Date column for the corresponding Number

Can anyone help me how to do this?

-Thanks N Regards,
Chinna.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-28 : 14:33:03

Is Final_Date a column in same table and you want to update it?

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-28 : 14:40:34

[code]
update table
set Final_Date=t.date1
from table
join
(select Number, max(date1) as date1 from table group by Number) as t
on table.Number=t.Number
[/code]
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-29 : 04:27:51

select Number, date1,max(date1)over(partition by number) as finaldate from table

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-04-29 : 06:49:15

HI, try this one also

select t.number,t.date1,maxdate from @temp t
inner join ( select number,max(date1) as maxdate from @temp group by number) t1
on t.number = t1.number

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources