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
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
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.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8975 Visits: 19028
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
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
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. Sad

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 Sad )?
DiverKas
DiverKas
SSC Veteran
SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)SSC Veteran (253 reputation)

Group: General Forum Members
Points: 253 Visits: 460
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


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
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.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



Thanks for the reply, unfortunately it doesnt work. Sad
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.
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
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.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10711 Visits: 12012
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

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: 9736
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. Sad

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 Sad )?


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.


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
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
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10711 Visits: 12012
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

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