SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How do I join tables, but select top 1 from 1-many tbl?


How do I join tables, but select top 1 from 1-many tbl?

Author
Message
ChrisM@home
ChrisM@home
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5296 Visits: 10608
Don. (10/13/2012)
Hi Guys,

Unfortunately neither of those scripts worked as there are multiple identical accountno and lastdate fields. Ideally Id need to filter on the History tables Recid field.

I've dummied up some data for you to look at


CREATE TABLE #CONTACT
(
ACCOUNTNO VARCHAR(5),
COMPANY VARCHAR(50),
CONTACT VARCHAR(50),
RECID VARCHAR(5)
)

CREATE TABLE #HISTORY
(
ACCOUNTNO VARCHAR(5),
LASTUSER VARCHAR(8),
LASTDATE DATE,
RECID VARCHAR(5)
)

INSERT INTO #CONTACT (ACCOUNTNO, COMPANY, CONTACT, RECID)
SELECT 'C1', 'ACME', 'WILEY COYOTE', 'CR1' UNION ALL
SELECT 'C2', 'Beta Ltd', 'Joe Soap', 'CR2'

INSERT INTO #HISTORY (ACCOUNTNO, LASTUSER, LASTDATE, RECID)
SELECT 'C1', 'JOE', '20120101', 'HR1' UNION ALL
SELECT 'C1', 'BOB', '20120101', 'HR2' UNION ALL
SELECT 'C1', 'JOE', '20120201', 'HR3' UNION ALL
SELECT 'C2', 'JOE', '20120202', 'HR4' UNION ALL
SELECT 'C2', 'BOB', '20120202', 'HR5'



How would I get a History select script to return HR1 and HR4?

Thanks


Don. (10/12/2012)
I'd like to get the join the tables, but only show the most recent record in History.


Why HR1? HR3 is on the most recent row. Assuming you mean HR3, then this works against the sample data set;

SELECT c.*, h.*  
FROM #Contact c
INNER JOIN (
SELECT h1.Accountno, h1.LastDate, RECID = MIN(h1.RECID)
FROM #History h1
INNER JOIN (
SELECT Accountno, MAX(LastDate) LastDate
FROM #History
GROUP BY Accountno
) l
ON l.Accountno = h1.Accountno
AND l.LastDate = h1.LastDate
GROUP BY h1.Accountno, h1.LastDate
) h
ON h.Accountno = c.Accountno




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Don.
Don.
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 271
L' Eomot Inversé (10/13/2012)Do you expect to get that? There's nothing in what you've told us about what you are doing that tells us that getting HR3 and HR5 wouldn't be OK (or for that matter HR3 and HR4 or HR2 and HR5 or ...)
You seem to have some rule that isn't at all clear from the data and what you've said - or I may be wrong, and you just haven't explained all the rules to us in language that I can understand.


Apologies, I was a bit hasty there.
In my example it should have returned HR3 and HR4 OR HR5.
Don.
Don.
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 271
ChrisM@home (10/14/2012)


Don. (10/12/2012)
I'd like to get the join the tables, but only show the most recent record in History.


Why HR1? HR3 is on the most recent row. Assuming you mean HR3, then this works against the sample data set;

SELECT c.*, h.*  
FROM #Contact c
INNER JOIN (
SELECT h1.Accountno, h1.LastDate, RECID = MIN(h1.RECID)
FROM #History h1
INNER JOIN (
SELECT Accountno, MAX(LastDate) LastDate
FROM #History
GROUP BY Accountno
) l
ON l.Accountno = h1.Accountno
AND l.LastDate = h1.LastDate
GROUP BY h1.Accountno, h1.LastDate
) h
ON h.Accountno = c.Accountno





Chris, thats great.
From the testing I've done so far, that looks perfect.

Thank you very much. BigGrin
Eric M Russell
Eric M Russell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29387 Visits: 11530
Don. (10/13/2012)
Hi Guys,

Unfortunately neither of those scripts worked as there are multiple identical accountno and lastdate fields. Ideally Id need to filter on the History tables Recid field.
...
...
In my example it should have returned HR3 and HR4 OR HR5.

Thanks

If running on SQL Server 2005+, the ROW_NUMBER() or RANK() function would be perfect for this.


select C_RECID, H_RECID from
(
select C.RECID C_RECID, H.RECID H_RECID,
row_number() over (partition by H.ACCOUNTNO order by H.RECID desc) history_rank
from #CONTACT C
join #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO
) x
where history_rank = 1;


C_RECID H_RECID
------- -------
CR1 HR3
CR2 HR5

You can simulate the functional equivalent in SQL Server 2000 less gracefully like so:


select C_RECID, H_RECID from
(
select C.RECID C_RECID, H.RECID H_RECID,
(select count(*) from #HISTORY HX
where HX.ACCOUNTNO = H.ACCOUNTNO and HX.RECID >= H.RECID) history_rank
from #CONTACT C
join #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO
) x
where history_rank = 1;


C_RECID H_RECID
------- -------
CR1 HR3
CR2 HR5

Either way, you would probably want a compound index on the HISTORY table's ACCOUNTNO and RECID columns. This would perhaps be the best suited primary key on the HISTORY table as well.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Don.
Don.
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 271
Eric M Russell (10/15/2012)
Don. (10/13/2012)
Hi Guys,

Unfortunately neither of those scripts worked as there are multiple identical accountno and lastdate fields. Ideally Id need to filter on the History tables Recid field.
...
...
In my example it should have returned HR3 and HR4 OR HR5.

Thanks

If running on SQL Server 2005+, the ROW_NUMBER() or RANK() function would be perfect for this.


select C_RECID, H_RECID from
(
select C.RECID C_RECID, H.RECID H_RECID,
row_number() over (partition by H.ACCOUNTNO order by H.RECID desc) history_rank
from #CONTACT C
join #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO
) x
where history_rank = 1;


C_RECID H_RECID
------- -------
CR1 HR3
CR2 HR5

You can simulate the functional equivalent in SQL Server 2000 less gracefully like so:


select C_RECID, H_RECID from
(
select C.RECID C_RECID, H.RECID H_RECID,
(select count(*) from #HISTORY HX
where HX.ACCOUNTNO = H.ACCOUNTNO and HX.RECID >= H.RECID) history_rank
from #CONTACT C
join #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO
) x
where history_rank = 1;


C_RECID H_RECID
------- -------
CR1 HR3
CR2 HR5

Either way, you would probably want a compound index on the HISTORY table's ACCOUNTNO and RECID columns. This would perhaps be the best suited primary key on the HISTORY table as well.



Thanks Eric, thats really informative.

I'll have a play with those scripts later.
ChrisM@home
ChrisM@home
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5296 Visits: 10608
Eric M Russell (10/15/2012)
You can simulate the functional equivalent in SQL Server 2000 less gracefully like so:


select C_RECID, H_RECID from
(
select C.RECID C_RECID, H.RECID H_RECID,
(select count(*) from #HISTORY HX
where HX.ACCOUNTNO = H.ACCOUNTNO and HX.RECID >= H.RECID) history_rank
from #CONTACT C
join #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO
) x
where history_rank = 1;


C_RECID H_RECID
------- -------
CR1 HR3
CR2 HR5


If Don has time, it would be interesting to know how this triangular join method compares to the aggregate methods. With small partitions it could be quite performant; with large partitions it won't. TJ's scale poorly.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Eric M Russell
Eric M Russell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29387 Visits: 11530
ChrisM@home (10/17/2012)
Eric M Russell (10/15/2012)
You can simulate the functional equivalent in SQL Server 2000 less gracefully like so:


select C_RECID, H_RECID from
(
select C.RECID C_RECID, H.RECID H_RECID,
(select count(*) from #HISTORY HX
where HX.ACCOUNTNO = H.ACCOUNTNO and HX.RECID >= H.RECID) history_rank
from #CONTACT C
join #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO
) x
where history_rank = 1;


C_RECID H_RECID
------- -------
CR1 HR3
CR2 HR5


If Don has time, it would be interesting to know how this triangular join method compares to the aggregate methods. With small partitions it could be quite performant; with large partitions it won't. TJ's scale poorly.

I agree the 2000 method would be potentially problematic in terms of optimization when compared to the the 2005+ windowing function method, although both of them could take considerable resources when dealing with million+ row tables. It's essential that HISTORY table be indexed in a way that supports it, perhaps even a covered indexed just to support this particular report, if it's called multiple times daily.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Want a cool Sig
Want a cool Sig
SSC Eights!
SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)SSC Eights! (980 reputation)

Group: General Forum Members
Points: 980 Visits: 705
If the records in history have the same date without a timestamp there needs to be a unique way to identify the latest record. If RecID is sequential then you don't even need the date.
From your dummy data and what you're looking for (H1, H4) it looks as if the RecID is sequential but in decending order which I'm assuming it's not.
Here's a modified script of the one above using the RecID.



CREATE TABLE #CONTACT
(
ACCOUNTNO VARCHAR(5),
COMPANY VARCHAR(50),
CONTACT VARCHAR(50),
RECID VARCHAR(5)
)

CREATE TABLE #HISTORY
(
ACCOUNTNO VARCHAR(5),
LASTUSER VARCHAR(8),
LASTDATE DATE,
RECID VARCHAR(5)
)

INSERT INTO #CONTACT (ACCOUNTNO, COMPANY, CONTACT, RECID)
SELECT 'C1', 'ACME', 'WILEY COYOTE', 'CR1' UNION ALL
SELECT 'C2', 'Beta Ltd', 'Joe Soap', 'CR2'

INSERT INTO #HISTORY (ACCOUNTNO, LASTUSER, LASTDATE, RECID)
SELECT 'C1', 'JOE', '20120101', 'HR1' UNION ALL
SELECT 'C1', 'BOB', '20120101', 'HR2' UNION ALL
SELECT 'C1', 'JOE', '20120201', 'HR3' UNION ALL
SELECT 'C2', 'JOE', '20120202', 'HR4' UNION ALL
SELECT 'C2', 'BOB', '20120202', 'HR5'


--Assuming that the RecID is sequential and ascending
select C.*, H.*
from #CONTACT C
left join (
select Hi.*
from #HISTORY Hi
inner join (
select max(recid) Min_RecID, ACCOUNTNO
from #HISTORY
group by ACCOUNTNO
) S
on Hi.ACCOUNTNO = S.ACCOUNTNO
and Hi.RECID = S.Min_RecID
) H
on C.ACCOUNTNO = H.ACCOUNTNO

--This works to get H1 and H4 but it implies that the recid is in descending order...
select C.*, H.*
from #CONTACT C
left join (
select Hi.*
from #HISTORY Hi
inner join (
select min(recid) Min_RecID, ACCOUNTNO
from #HISTORY
group by ACCOUNTNO
) S
on Hi.ACCOUNTNO = S.ACCOUNTNO
and Hi.RECID = S.Min_RecID
) H
on C.ACCOUNTNO = H.ACCOUNTNO

drop table #Contact
drop table #History



---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar Hehe
I want a personal webpage Cool
I want to win the lotto :-D
I want a gf like Tiffa w00t Oh wait I'm married!:-D
Don.
Don.
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 271
Hi Guys,

I'll try and make some time during the week to have a play with this and report back. Otherwise I'll spend some time over the weekend.

Thanks again, for all your help.
Don.
Don.
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 271
ChrisM@home (10/17/2012)
Eric M Russell (10/15/2012)



If Don has time, it would be interesting to know how this triangular join method compares to the aggregate methods. With small partitions it could be quite performant; with large partitions it won't. TJ's scale poorly.


Hi guys,

I've converted the 3 scripts so they now run and return the same data.

The script results were:
Script 1: 05 seconds returning 5343 records (ChrisMs script)
Script 2: 02 seconds returning 5343 records (Erics 2005 script)
Script 3: 1.36 returning 5343 records (Erics 2000 script)

The first script initially took 32 seconds to return 5550, but I found that Id messed up the joins.

Thanks for the lesson. ;o)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search