Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How do I join tables, but select top 1 from 1-many tbl? Expand / Collapse
Author
Message
Posted Friday, October 12, 2012 9:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:56 AM
Points: 46, Visits: 225
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.
Post #1372219
Posted Friday, October 12, 2012 9:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:41 AM
Points: 6,748, Visits: 12,843
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
Post #1372256
Posted Friday, October 12, 2012 10:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:56 AM
Points: 46, Visits: 225
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 )?
Post #1372274
Posted Friday, October 12, 2012 11:45 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, 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

Post #1372306
Posted Friday, October 12, 2012 3:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:56 AM
Points: 46, Visits: 225
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.
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.


Post #1372372
Posted Saturday, October 13, 2012 4:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:56 AM
Points: 46, Visits: 225
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.
Post #1372411
Posted Saturday, October 13, 2012 2:10 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:56 PM
Points: 8,271, Visits: 8,717
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
Post #1372462
Posted Saturday, October 13, 2012 2:30 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:59 PM
Points: 1,058, Visits: 5,740
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.



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
Post #1372466
Posted Saturday, October 13, 2012 4:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:56 AM
Points: 46, Visits: 225
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
Post #1372475
Posted Saturday, October 13, 2012 7:19 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 4:56 PM
Points: 8,271, Visits: 8,717
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
Post #1372479
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse