Need help for SQL select query for this two table

Question

Table Capture image : http://img844.imageshack.us/img844/6213/99730337.jpg

------------ PositionTable------------
ID    ContentFK   Position
11     100               1
12     101               1
13     104               2
14     102               2
15     103               2
16     105               3
17     106               3
18     107               2


----------Content Table ------------
ContentID   UpdateDate  Title
100          11.10.2009    Aol
101          12.10.2009    Microsoft
102          12.10.2009    e-Bay
103          12.11.2009    google
104          16.11.2009    novell
105          17.11.2009    asus
106          16.11.2009    nokia
107          11.11.2009    samsung

Who can help me to the question between the two tables My scenario.

Sort by number as Position 1,2,3. However, a number of the groups to list only one record (order by Position ASC (Position: 1,2,3)

With PositionTable.ContentFK = ContentTable.ContentID by UpdateDate of last update in ContentTablo

How I can get list same as result.

p.Postion     p.ID            p.ContentFK     c.UpdateDate      c.Title

1             12              101             12.10.2009        Microsoft 
2             13              104             16.11.2009        novell 
3             16              105             17.11.2009        asus

Thanks all,

Answer 1

This should do it:

EDIT: Original code was because I thought your dates were MM.DD.YYYY. Realized your dates were DD.MM.YYYY and adjusted code accordingly.

EDIT 2: Changed answer based on feedback for UpdateDate datatype.

;with cteMaxDate as (
    select p.Position, MAX(c.UpdateDate) as MaxDate
        from PositionTable p
            inner join ContentTable c
                on p.ContentFK = c.ContentID
        group by p.Position
)
select p.Position, p.ID, p.ContentFK, c.UpdateDate, c.Title
    from cteMaxDate m
        inner join PositionTable p
            on m.Position = p.Position
        inner join ContentTable c
            on p.ContentFK = c.ContentID
                and m.MaxDate = c.UpdateDate

Answer 2

The subquery's a bit ugly (and correlated), but this is the simplest way I can think of doing it.

Assuming your UpdateDate column is DATETIME - otherwise you'll have to do something like Joe did (or, better, change the column to DATETIME :-)

select p.Position
    ,p.ID
    ,p.ContentFK
    ,c.UpdateDate
    ,c.Title
from PositionTable p
join ContentTable c
    on p.ContentFK = c.ContentID
    and c.UpdateDate = (select max(ic.UpdateDate) from ContentTable ic
                            join PositionTable ip
                                on ip.ContentFK = ic.ContentID
                            where ip.Position = p.Position)
order by p.Position 

Answer 3

Try:

select Position,  ID, ContentFK, UpdateDate, Title
from (
select  p.Position,
    p.ID,
    p.ContentFK,
    c.UpdateDate,
    c.Title,
    row_number() over (partition by p.Position order by p.Position, c.UpdateDate desc) as num
from    Position p inner join
    Content c on p.ContentFK = c.ContentId ) a
where   num = 1

Answer 4

Thank Joe and Paul. I have get same result. Which query is better performance. ?