join but return only first match

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

General SQL Server Forums

New to SQL Server Programming

join but return only first match

Author

Topic

dpres
Starting Member

4 Posts

Posted - 2011-02-13 : 22:00:46

How can I pair two records by date, but only use each record once?

table pt has name(varchar), dateSeen(datetime)
table pr has name(varchar), followup(datetime)

select pt.*,pr.* from pt join pr on pr.followup between pt.dateseen and dateadd(HOUR,36,pt.dateseen)

There may be multiple records in pr meeting the date range, but I want to return only the first match. the above select statement returns all the matches.

Smith, 3/16/2010@1300,Smith, 3/17/2010@1000
Smith, 3/16/2010@1300,Smith, 3/18/2010@2100
Smith, 3/16/2010@1300,Smith, 3/18/2010@2137

Thanks so much for your help!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-13 : 22:07:50

[code]
select *
from
(
select pt.*,pr.*, row_no = row_number() over (partition by pt.name order by pr.followup)
from pt join pr on pr.followup between pt.dateseen and dateadd(HOUR,36,pt.dateseen)
) t
where r.row_no = 1[/code]

KH
[spoiler]Time is always against us[/spoiler]

dpres
Starting Member

4 Posts

Posted - 2011-02-14 : 10:44:05

Thank you. That does match just the first record. I'm also seeing the pr record being matched multiple times. Is there a way to return the first match, but only allow each record to be matched once?

Smith, 3/16/2010@1300,Smith, 3/17/2010@1000
Smith, 3/17/2010@1300,Smith, 3/17/2010@1000
Smith, 3/16/2010@1300,Smith, 3/18/2010@2137

dpres
Starting Member

4 Posts

Posted - 2011-02-15 : 13:28:23

Suggestions? I'm stumped.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-15 : 13:32:57

quote:
Originally posted by dpres

Suggestions? I'm stumped.

Come up with a couple of rows for your both tables and the desired output format in light of those records!!!

e.g. Pr table

Name, dateseen
abc,2011-01-01

output
Col1,col2,col3,col4
value1,value2,value3,value4

dpres
Starting Member

4 Posts

Posted - 2011-02-16 : 07:57:41

pt
Smith, 3/16/2010@1300
Smith, 3/17/2010@1300
Smith, 3/16/2010@1300

pr
Smith, 3/17/2010@1000
Smith, 3/17/2010@1000
Smith, 3/18/2010@2137

results in these pairs
Smith, 3/16/2010@1300 Smith, 3/17/2010@1000
Smith, 3/17/2010@1300 Smith, 3/17/2010@1000 (already paired in row above)
Smith, 3/17/2010@1330 Smith, 3/18/2010@2137

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources