What is the easiest way to select the Max of a field 1

[chrisdoesstuff]

SQL server 2008 Maybe I'm missing something easy, at least it seems so but I have a datetime field that I need to select the max of. I was hoping I could do this as part of my join but it might be more work than that.

The data is as follows
Table: TeamTable
Columns: Team_Name, Last_Changed_Time

The query is

LEFT OUTER JOIN Teamtable T
ON O.ObjectID = t.ObjectID
AND t.team_name = 'blue'

What I want is
AND Last_Changed_Time = MAX(Last_Changed_time)

I know that I can't do that since it didn't work but what might be the best way to accomplish this. It seems like it should be quick but I'm not coming up with it. Any help is appreciated. Thanks

 

[bborissov]

LEFT JOIN (SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY ObjectID 
                              ORDER BY Last_Changed_Time DESC) 
                        AS  RowNum
           FROM Teamtable) T
ON O.ObjectID = t.ObjectID
AND t.team_name = 'blue'
AND t.RowNum = 1

NOT TESTED!!!!!!!


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[jbenson001]

How about:

At the top of your query
Declare @lastchangedtime <put data type here>
Set @lastchangedtime = ( Select MAX(Last_Changed_time) From <table> Where ....)
Then in your query, instead of
[quote]AND Last_Changed_Time = MAX(Last_Changed_time)[/quote]
use 
AND Last_Changed_Time = @lastchangedtime

 

[bborissov]

The only think against such method if that you will get the MAX date for whole table, not for ObjectID.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[jbenson001]

True, but the OP was not clear on what he/she wanted. I initially interpreted it as the whole table.

So, to the OP, you can just change the select statement in my example to include the join.

 

[bborissov]

Agreed

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[chrisdoesstuff]

Thanks for your replies. I think Boris hit it on the head so I need to go work to apply that logic to my query.

 

[jbenson001]

Borris's code is more SQL savvy and uses some cool features of new SQL Server versions. The Partition function can be very powerful. I would go that route as well.