sql server - Need help for SQL select query for this two table -

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 me question between 2 tables scenario.

sort number position 1,2,3. however, number of groups list 1 record (order position asc (position: 1,2,3)

with positiontable.contentfk = contenttable.contentid updatedate of last update in contenttablo

how can list same 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,

this should it:

edit: original code because thought dates mm.dd.yyyy. realized dates dd.mm.yyyy , adjusted code accordingly.

edit 2: changed answer based on feedback updatedate datatype.

;with ctemaxdate (     select p.position, max(c.updatedate) maxdate         positiontable p             inner join contenttable c                 on p.contentfk = c.contentid         group p.position ) select p.position, p.id, p.contentfk, c.updatedate, c.title     ctemaxdate m         inner join positiontable p             on m.position = p.position         inner join contenttable c             on p.contentfk = c.contentid                 , m.maxdate = c.updatedate