Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mattt
Posting Yak Master
194 Posts |
 Posted - 2008-02-05 : 08:42:02
|
| Hi,In my data structure I have two related tables - Advertising and AdvertisingNotes. One Advert in Advertising may have multiple records in AdvertisingNotes.What I want to do is a cross-table select (easy enough it itself) between Advertising and AdvertisingNotes which returns only one row per advert and this row must contain the most recent (highest date) matching record from AdvertisingNotes.Is this possible?Cheers,Matt |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-02-06 : 10:49:06
|
| Yes , you could do something like, :SELECT myCols FROM Advertising as aINNER JOIN (SELECT TOP 1 myCols FROM AdvertisingNotes as an WHERE a.ID = an.ID ORDER by myDate DESC) an2ON a.ID = an2.IDJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-02-06 : 10:53:26
|
| SELECT t.ID,t.myColsFROM(SELECT ROW_NUMBER() OVER(PARTITION BY a.ID ORDER BY an.NotesID DESC) AS RowNo,a.ID, an.myCols FROM Advertising as aINNER JOIN AdvertisingNotes as an ON a.ID = an.ID )tWHERE t.RowNo=1 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
Get only one row on cross-table query