return only the record with the latest date

Please use like this.

SELECT * FROM 
(
	SELECT ID ,MAX(createdate) createdate   
	From Table
	where enddate ='12/1/89'
	GROUP BY ID
)k
INNER JOIN Table tr ON tr.createdate = k.createdate AND k.Id = tr.Id

Select allOpen in new window

We can also use CTE..

;WITH CTE AS
(
	SELECT ID ,MAX(createdate) createdate   
	From Table
	where enddate ='12/1/89'
	GROUP BY ID
)
SELECT tr.* FROM CTE k INNER JOIN Table tr ON tr.createdate = k.createdate AND k.Id = tr.Id

Select allOpen in new window

We can also use ranking functions for this -

;WITH CTE AS
(
	SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY createdate DESC) rnk
	From Table
	where enddate ='12/1/89'	
)
SELECT ID,OrderID,createdate,enddate,Field1,Field3,field4
FROM CTE
WHERE rnk = 1

Select allOpen in new window

Pawan's second comment is closest to how I would solve it but not entirely what was asked.  Here's my version:

;with MaxDatesPerOrderID as (
	SELECT OrderID, MAX(createdate) createdate
	From Table
	group by OrderID
)
select * from table t
inner join MaxDatesPerOrderID md on md.OrderID = t.OrderID
	and md.createdate= t.createdate;

Select allOpen in new window

The query assumes that the OrderID and createdate combination is unique over the whole table.

Edit: replaced "enddate" with "createdate" - also, add where clause if it is needed

@ValentinoV
- Your solution is incorrect. this is what is asked. " I want is the return the newest create date" . You are using enddate and Where clause is also missing.

@Author --updated for OrderId. Everything is same just change column Id to Orderid.

;WITH CTE AS
(
	SELECT *,ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY createdate DESC) rnk
	From Table
	where enddate ='12/1/89'	
)
SELECT ID,OrderID,createdate,enddate,Field1,Field3,field4
FROM CTE
WHERE rnk = 1

Select allOpen in new window

OR this

;WITH CTE AS
(
	SELECT OrderID ,MAX(createdate) createdate   
	From Table
	where enddate ='12/1/89'
	GROUP BY OrderID 
)
SELECT tr.* FROM CTE k INNER JOIN Table tr ON tr.createdate = k.createdate AND k.OrderID = tr.OrderID 

Select allOpen in new window

Select top 1

and sort on date?

@Thierry - That will not work. With your code you will only 1 record.

We need latest record for each OrderId.

Hi ValentinoV - This is a good suggestion.

@Author please use below in the where clause..

where enddate ='1989-12-01'

Select allOpen in new window

OR

where enddate ='19891201'

Select allOpen in new window

---> This is I normally prefer.

ty