October 12, 2012 at 9:16 am
Essentially I have a Contact table and a History table.
I'd like to get the join the tables, but only show the most recent record in History.
How would I join the tables, but only select the top 1 record from the history table?
Contact
Accountno Company, Contact, Recid
History
Accountno, LastUser, LastDate, Recid
Accountno is specific to each contact.
Recid is unique to each record
I hope this isnt to to vague
Thanks for your help.
October 12, 2012 at 9:57 am
Don. (10/12/2012)
Essentially I have a Contact table and a History table.I'd like to get the join the tables, but only show the most recent record in History.
How would I join the tables, but only select the top 1 record from the history table?
Contact
Accountno Company, Contact, Recid
History
Accountno, LastUser, LastDate, Recid
Accountno is specific to each contact.
Recid is unique to each record
I hope this isnt to to vague
Thanks for your help.
SELECT c.*, h.*
FROM Contact c
OUTER APPLY (
SELECT TOP 1 h.*
FROM History h
WHERE h.Accountno = c.Accountno
ORDER BY h.LastDate DESC
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
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
October 12, 2012 at 10:31 am
Chris that looks almost perfect, thank you. (Needed x instead of h in the initial select)
SELECT c.*, x.*
FROM Contact c
OUTER APPLY (
SELECT TOP 1 h.*
FROM History h
WHERE h.Accountno = c.Accountno
ORDER BY h.LastDate DESC
) x
I amended my script, and tested, but our clients dont have a recent backend and they're still a ways off being upgraded. 🙁
OUTER APPLY is new to me, but looks really interesting \ useful. I'll definitely read up on it.
Any suggestions as to how Id be able to get the same results except using scripts for an older backend( SQL 2000 🙁 )?
October 12, 2012 at 11:45 am
For older environments or other than T-SQL flavor, more generically would be something like (untested):
SELECT c.Accountno,C.Company,C.Contact,C.Recid,H.lastuser,H.lastdate
FROM contact C
LEFT JOIN history h ON C.accountno=h.accountno
LEFT JOIN (SELECT accountno,MAX(lastdate) AS lastdate
FROM history
GROUP BY accountno) mx ON h.accountno=mx.accountno
AND h.lastdate=mx.lastdate
October 12, 2012 at 3:12 pm
DiverKas (10/12/2012)
For older environments or other than T-SQL flavor, more generically would be something like (untested):
SELECT c.Accountno,C.Company,C.Contact,C.Recid,H.lastuser,H.lastdateFROM contact C
LEFT JOIN history h ON C.accountno=h.accountno
LEFT JOIN (SELECT accountno,MAX(lastdate) AS lastdate
FROM history
GROUP BY accountno) mx ON h.accountno=mx.accountno
AND h.lastdate=mx.lastdate
Thanks for the reply, unfortunately it doesnt work. 🙁
Since h.accountno returns 1 of each accountno, its not limiting the h.accountno results to only one record per accountno and all history records are being returned.
If we could do that with the recid field, it should work, although I currently have no clue how to do that.
October 13, 2012 at 4:32 am
How would I go about selecting a distinct recid from History?
While the script below does return the correct data, when we join it on accountno and lastdate, it will include records with identical accountnos and recids (Ie If we've logged calls \ emails to the same contact multiple times on the same day)
How would I go about getting the recids of the records returned in this script?
SELECT accountno,MAX(lastdate) AS lastdate
FROM history
GROUP BY accountno
If I had the recids of the history records, I could use them as in the script below...
SELECT c.Accountno,C.Company, C.Contact,C.Recid,H.lastuser,H.lastdate
FROM contact C
LEFT JOIN history h ON C.accountno=h.accountno and h.RECID IN (....
Any suggestions would be greatly appreciated.
October 13, 2012 at 2:10 pm
If I've understood correctly what is wanted, it could be
select h.*, c.*
from Contact c
inner join (
select top 1 h1.* from History h1
inner join (select Accountno, max(LastDate) LastDate from History group by Accountno) L
on h1.Accountno = L.Accountno and h1.LastDate = L.LastDate) h
on h.Accountno = c.Accountno
Tom
October 13, 2012 at 2:30 pm
Don. (10/12/2012)
Chris that looks almost perfect, thank you. (Needed x instead of h in the initial select)
SELECT c.*, x.*FROM Contact c
OUTER APPLY (
SELECT TOP 1 h.*
FROM History h
WHERE h.Accountno = c.Accountno
ORDER BY h.LastDate DESC
) x
I amended my script, and tested, but our clients dont have a recent backend and they're still a ways off being upgraded. 🙁
OUTER APPLY is new to me, but looks really interesting \ useful. I'll definitely read up on it.
Any suggestions as to how Id be able to get the same results except using scripts for an older backend( SQL 2000 🙁 )?
Sorry about the late reply Don, had some issues with a drive on this lappy.
Tom's solution should work just fine. The TOP 1 may not be necessary, it's a tiebreaker in case you have more than one row with the same lastdate which also happens to be the MAX lastdate.
The following will work in cases where the business logic excludes such dupes from occurring, which may well be the case if lastdate is DATETIME - and it almost certainly is.
SELECT c.Accountno, C.Company, C.Contact, C.Recid, H.lastuser, H.lastdate
FROM contact C
LEFT JOIN (
SELECT accountno, MAX(lastdate) AS lastdate
FROM history
GROUP BY accountno
) mx ON mx.accountno = h.accountno
LEFT JOIN history h
ON h.accountno = mx.accountno
AND h.lastdate = mx.lastdate
Edit: having said that, I'd advocate Tom's approach as sound, sensible defensive coding.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 13, 2012 at 4:16 pm
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
(
ACCOUNTNOVARCHAR(5),
COMPANYVARCHAR(50),
CONTACTVARCHAR(50),
RECIDVARCHAR(5)
)
CREATE TABLE #HISTORY
(
ACCOUNTNOVARCHAR(5),
LASTUSERVARCHAR(8),
LASTDATEDATE,
RECIDVARCHAR(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
October 13, 2012 at 7:19 pm
Don. (10/13/2012)
Hi Guys,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'
[/code]
How would I get a History select script to return HR1 and HR4?
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.
Tom
October 14, 2012 at 2:53 am
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
(
ACCOUNTNOVARCHAR(5),
COMPANYVARCHAR(50),
CONTACTVARCHAR(50),
RECIDVARCHAR(5)
)
CREATE TABLE #HISTORY
(
ACCOUNTNOVARCHAR(5),
LASTUSERVARCHAR(8),
LASTDATEDATE,
RECIDVARCHAR(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
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 14, 2012 at 5:14 am
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.
October 14, 2012 at 5:22 am
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. 😀
October 15, 2012 at 10:26 am
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 16, 2012 at 4:36 am
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.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply