How to show fields from most recently added detail in a view?

QUERY:

drop table #foot
create table #foot (
    id int primary key not null,
    name varchar(50) not null
)
go

drop table #note
create table #note (
    id int primary key not null,
    note varchar(MAX) not null,
    foot_id int not null references #foot(id)
)
go

insert into #foot values 
(1, 'Joe'), (2, 'Mike'), (3, 'Rob')
go

insert into #note (id, note, foot_id) values (1, 'Joe note 1', 1)
go
insert into #note (id, note, foot_id) values(2, 'Joe note 2', 1)
go
insert into #note (id, note, foot_id) values(3, 'Mike note 1', 2)
go

select F.name, N.note, N.id
from #foot F left outer join #note N on N.foot_id=F.id

RESULT:

QUESTION:

How can I create a view/query resulting in one row for each master record (#foot) along with fields from the most recently inserted detail (#note), if any?

GOAL:

(NOTE: the way I would tell which one is m开发者_JAVA百科ost recent is the id which would be higher for newer records)

---

select t.name, t.note, t.id
    from (select F.name, N.note, N.id,
                 ROW_NUMBER() over(partition by F.id order by N.id desc) as RowNum
              from #foot F 
                  left outer join #note N 
                      on N.foot_id=F.id) t
    where t.RowNum = 1

---

Assuming the ID created in the #note table is always incremental (imposed by using IDENTITY or by controlling the inserts to always increment the by by max value) you can use the following query (which uses rank function):

WITH Dat AS
(
    SELECT f.name, 
             n.note, 
             n.id,
             RANK() OVER(PARTITION BY n.foot_id ORDER BY n.id DESC) rn
    FROM #foot f LEFT OUTER JOIN #note n 
    ON n.foot_id = f.id  
)
SELECT *
  FROM Dat
    WHERE rn = 1