Click here to monitor SSC
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 9734
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
   Wink 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.
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 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.
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 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
   Wink 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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4580 Visits: 9502
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.
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1161 Visits: 9734
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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4580 Visits: 9502
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
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 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
               Wink S
      on Hi.ACCOUNTNO = S.ACCOUNTNO
      and Hi.RECID = S.Min_RecID
      Wink 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
               Wink S
      on Hi.ACCOUNTNO = S.ACCOUNTNO
      and Hi.RECID = S.Min_RecID
      Wink 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.
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 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.
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 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