Latest Dates

[Bill]

Hi,

I would need your help to set the query for searching the latest date of any
five dates.

example:
Field1 Field2
***** ******
Date1 - 03/02/2004
Date2 - 02/02/2003
Date3 - 04/07/2002
Date4 - 05/06/2002
Date5 - 06/09/2004

I would like to create two new columns to give me the latest Date and Date#.
from any of Date1 to Date5.

Field3 Field4
***** ******
Date5 06/09/2004

Your help would be much appreciated.
Thanks

 

[Mr Sir]

Maybe try this:

SELECT Field1, MAX(Field2) As [Latest Date]
FROM tablename
GROUP BY Field1

 

[Billy Yao [MSFT]]

Hi Bill,

If you would like to select the latest date, there may need no Group By clause. Otherwise, the result will be
the latest date in the Date group (Date1, Date2, .....).

Not sure if you'd really like to add the two new columns in the original table. If that, the statement may look
like:

Alter Table TableName Add Field3 CHAR(10), Field4 VARCHAR(30)
Go

Insert Into TableName(Field3, Field4)
Select Field1, Field2 As [Late Date] From TableName
Where Field2 in
(
Select max(Field2) From TableName
)

Regards,

Billy Yao
Microsoft Online Support