question

There are two ways that come to my mind now:

create table testnotes            
(            
noteid int,             
notedate datetime,            
description varchar(20)            
)            
go            
insert testnotes(noteid, notedate, description)            
select 1, '20100501', '1st oldest note' union all            
select 1, '20100502', '1st newer note' union all            
select 1, '20100503', '1st newest note' union all            
select 2, '20100504', '2nd oldest note' union all            
select 2, '20100505', '2nd newer note' union all            
select 2, '20100506', '2nd newest note'             
go            
--first way:            
select noteid, notedate, description            
from            
(            
    select noteid, notedate, description,             
    row_number() over(partition by noteid order by notedate desc) rn            
    from testnotes            
) q            
where rn = 1            
go            
--second way            
select a.noteid, a.notedate, a.description            
from testnotes a inner join             
    (select max(notedate) notedate, noteid             
    from testnotes group by noteid) b on a.noteid = b.noteid and a.notedate = b.notedate            
go            
drop table testnotes            
go            

First way as you see, uses row_number() ranking function in subquery, and outer query selects only rows with rn equal 1. This approach has the advantage of ability to return top x rows from particular note. Second way uses derived table query that returns noteid and max date for this note. Then you join it to testnotes table to get other fields, in this case description. You have to join by noteid and notedate to avoid situation when two notes have the same update date and result in incorrect join output.

Starting from SQL server 2005 you can use ROW_NUMBER to solve your problem like this:

            
SELECT ID, date, description            
  FROM (SELECT ID, date, description,            
                ROW_NUMBER() OVER(PARTITION BY ID ORDER BY date DESC) AS row_num            
          FROM tbl1) AS T            
 WHERE row_num = 1