Many to Many - Select the latest

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Many to Many - Select the latest

Author

Topic

bakerlp
Starting Member

4 Posts

Posted - 2010-07-11 : 04:03:37

Hi,

I'm after some assistance selecting the latest records (by date) in a many to many relationship.

Database Design is as follows:

Frame (Table)
{
FrameId Int
FrameName varchar
}

Source (Table)
{
SourceId Int
DataGenerated DateTime
}

FrameSource (Table)
{
FrameSourceId
SourceId
FrameId
}

I would like to be able to select the latest 'Source' records for a group of Frames. Hence the results could be say

FrameId = 1 LatestSourceId = 1
FrameId = 2 LatestSourceId = 1
FrameId = 3 LatestSourceId = 2
FrameId = 4 LatestSourceId = 6
FrameId = 5 LatestSourceId = 2

From this I can then choose which Source records can be deleted as not being used for the latest frames i.e records 3, 4, 5.

Thanks in advance
LP

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-11 : 04:09:34

[code]
select
FS.FrameID
,max(S.DataGenerated) as latest_Source
from FrameSource as FS
join Source as S
on S.SourceID = FS.SourceID
Group by FS.FrameID
order by FS.FrameID
[/code]

Kristen
Test

22859 Posts

Posted - 2010-07-11 : 04:17:42

[code]
SELECT FrameId,
SourceId AS [LatestSourceId]
FROM
(
SELECT [T_RowNumber] = ROW_NUMBER()
OVER
(
PARTITION BY FrameId
ORDER BY FrameId, SourceId DESC
),
X.*
FROM
(
SELECT FrameId, SourceId
FROM FrameSource
) AS X
) AS T
WHERE T_RowNumber = 1
ORDER BY FrameId
[/code]

Kristen
Test

22859 Posts

Posted - 2010-07-11 : 04:19:06

Doh! Yes, slimt_slimt solution much better (assuming you don't want any other columns from the row)

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-11 : 09:11:57

Kristen, yours is very SQL2005ish and nice. :) but yes, if you want to return more than one column from select list, your query is the one to use.

bakerlp
Starting Member

4 Posts

Posted - 2010-07-11 : 10:31:15

Guys, many thanks for your help. As I wanted the SourceId, I went for the kristen's. Thanks again, it worked a dream.

bakerlp
Starting Member

4 Posts

Posted - 2010-07-11 : 10:42:55

Sorry, the only problem with kristen's approach is that is doesn't use the date and hence if a lower SourceId has a later date, the wrong id is selected.

bakerlp
Starting Member

4 Posts

Posted - 2010-07-11 : 10:52:03

I've managed to tweek kristens to work regardless of the id...what do you think?

SELECT FrameId,SourceId
FROM
(
SELECT [T_RowNumber] = ROW_NUMBER()
OVER
(
PARTITION BY FrameId
ORDER BY FrameId, LatestDate DESC
),
X.*
FROM
(
SELECT FrameId, FS.SourceId, S.Date As LatestDate
FROM FrameSource FS
Inner join Source S on S.SourceId = FS.SourceId
) AS X
) AS T
WHERE T_RowNumber = 1
ORDER BY FrameId

Kristen
Test

22859 Posts

Posted - 2010-07-12 : 03:02:37

Sorry, didn't read the question properly. What you have looks fine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources