InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 02:32:41 GMT20http://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]chandan.kumar (10/30/2012)[/b][hr]Hi Don,Can you please check below query - SELECT c.*, h.*FROM #CONTACT cINNER JOIN (SELECT Accountno, ROW_NUMBER() OVER(PARTITION BY accountno ORDER BY lastdate) Rownum ,RECID FROM #History GROUP BY Accountno,LastDate,RECID) hON c.ACCOUNTNO=h.ACCOUNTNOWHERE h.Rownum=1[/quote]Always read the whole thread. It's a [i]spec[/i].[quote][b]Don. (10/12/2012)[/b][hr]Any suggestions as to how Id be able to get the same results except using scripts for an older backend( SQL 2000 :( )?[/quote]Tue, 30 Oct 2012 03:41:23 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspxHi Don,Can you please check below query - SELECT c.*, h.*FROM #CONTACT cINNER JOIN (SELECT Accountno, ROW_NUMBER() OVER(PARTITION BY accountno ORDER BY lastdate) Rownum ,RECID FROM #History GROUP BY Accountno,LastDate,RECID) hON c.ACCOUNTNO=h.ACCOUNTNOWHERE h.Rownum=1Tue, 30 Oct 2012 03:38:09 GMTchandan.kumarhttp://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]Lee Crain (10/29/2012)[/b][hr]Don.,Are you using a Touchstar system database?[/quote]Hi Lee,Its for a CMS product called Goldmine.Mon, 29 Oct 2012 12:52:43 GMTDon.http://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspxDon.,Are you using a Touchstar system database?Mon, 29 Oct 2012 12:13:23 GMTGail Wanabeehttp://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspxI'll have a read.Thanks Chris.Sun, 28 Oct 2012 04:35:55 GMTDon.http://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]Don. (10/28/2012)[/b][hr][quote][b]ChrisM@home (10/28/2012)[/b][hr]Thanks for the feedback, Don. The script in bold - is it the 2000-compatible one?[/quote]Morning Chris,It was the following script you gave me help with:[code="sql"]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) hON h.Accountno = c.Accountno[/code]I plugged it into my existing script and got the results needed.[/quote]Thanks Don - that's the 2000-compatible script. If you're curious about the performance of the triangular join method posted by Eric, you will find the answer in Jeff Moden's excellent article linked in my sig, bottom left.Sun, 28 Oct 2012 04:15:22 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]ChrisM@home (10/28/2012)[/b][hr]Thanks for the feedback, Don. The script in bold - is it the 2000-compatible one?[/quote]Morning Chris,It was the following script you gave me help with:[code="sql"]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) hON h.Accountno = c.Accountno[/code]I plugged it into my existing script and got the results needed.Sun, 28 Oct 2012 04:02:43 GMTDon.http://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]Don. (10/28/2012)[/b][hr][quote][b]ChrisM@home (10/17/2012)[/b][hr][quote][b]Eric M Russell (10/15/2012)[/b][hr][/quote]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.[/quote]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 ([b]ChrisMs script[/b])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)[/quote]Thanks for the feedback, Don. The script in bold - is it the 2000-compatible one?Sun, 28 Oct 2012 03:54:35 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]ChrisM@home (10/17/2012)[/b][hr][quote][b]Eric M Russell (10/15/2012)[/b][hr][/quote]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.[/quote]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)Sun, 28 Oct 2012 03:46:04 GMTDon.http://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspxHi 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.Wed, 17 Oct 2012 10:03:08 GMTDon.http://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspxIf 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.[code="sql"]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 ALLSELECT 'C2', 'Beta Ltd', 'Joe Soap', 'CR2'INSERT INTO #HISTORY (ACCOUNTNO, LASTUSER, LASTDATE, RECID)SELECT 'C1', 'JOE', '20120101', 'HR1' UNION ALLSELECT 'C1', 'BOB', '20120101', 'HR2' UNION ALLSELECT 'C1', 'JOE', '20120201', 'HR3' UNION ALLSELECT 'C2', 'JOE', '20120202', 'HR4' UNION ALLSELECT 'C2', 'BOB', '20120202', 'HR5'--Assuming that the RecID is sequential and ascendingselect C.*, H.*from #CONTACT Cleft 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 ) Hon 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 Cleft 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 ) Hon C.ACCOUNTNO = H.ACCOUNTNOdrop table #Contactdrop table #History[/code]Wed, 17 Oct 2012 08:54:32 GMTWant a cool Sighttp://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]ChrisM@home (10/17/2012)[/b][hr][quote][b]Eric M Russell (10/15/2012)[/b][hr]You can simulate the functional equivalent in SQL Server 2000 less gracefully like so:[code="sql"]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[size="4"][b] >= [/b][/size]H.RECID) history_rankfrom #CONTACT Cjoin #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO) xwhere history_rank = 1;[/code]C_RECID H_RECID------- -------CR1 HR3CR2 HR5[/quote]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.[/quote]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.Wed, 17 Oct 2012 07:40:40 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]Eric M Russell (10/15/2012)[/b][hr]You can simulate the functional equivalent in SQL Server 2000 less gracefully like so:[code="sql"]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[size="4"][b] >= [/b][/size]H.RECID) history_rankfrom #CONTACT Cjoin #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO) xwhere history_rank = 1;[/code]C_RECID H_RECID------- -------CR1 HR3CR2 HR5[/quote]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.Wed, 17 Oct 2012 02:37:08 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]Eric M Russell (10/15/2012)[/b][hr][quote][b]Don. (10/13/2012)[/b][hr]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[/quote]If running on SQL Server 2005+, the ROW_NUMBER() or RANK() function would be perfect for this.[code="sql"]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_rankfrom #CONTACT Cjoin #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO) xwhere history_rank = 1;[/code]C_RECID H_RECID------- -------CR1 HR3CR2 HR5You can simulate the functional equivalent in SQL Server 2000 less gracefully like so:[code="sql"]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_rankfrom #CONTACT Cjoin #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO) xwhere history_rank = 1;[/code]C_RECID H_RECID------- -------CR1 HR3CR2 HR5Either 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.[/quote]Thanks Eric, thats really informative.I'll have a play with those scripts later.Tue, 16 Oct 2012 04:36:22 GMTDon.http://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]Don. (10/13/2012)[/b][hr]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[/quote]If running on SQL Server 2005+, the ROW_NUMBER() or RANK() function would be perfect for this.[code="sql"]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_rankfrom #CONTACT Cjoin #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO) xwhere history_rank = 1;[/code]C_RECID H_RECID------- -------CR1 HR3CR2 HR5You can simulate the functional equivalent in SQL Server 2000 less gracefully like so:[code="sql"]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_rankfrom #CONTACT Cjoin #HISTORY H on H.ACCOUNTNO = C.ACCOUNTNO) xwhere history_rank = 1;[/code]C_RECID H_RECID------- -------CR1 HR3CR2 HR5Either 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.Mon, 15 Oct 2012 10:26:46 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]ChrisM@home (10/14/2012)[/b][hr][quote][b]Don. (10/12/2012)[/b][hr]I'd like to get the join the tables, but only show the most recent record in History.[/quote]Why HR1? HR3 is on the most recent row. Assuming you mean HR3, then this works against the sample data set;[code="sql"]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) hON h.Accountno = c.Accountno[/code][/quote]Chris, thats great.From the testing I've done so far, that looks perfect. Thank you very much. :DSun, 14 Oct 2012 05:22:52 GMTDon.http://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]L' Eomot Inversé (10/13/2012)[/b]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.[/quote]Apologies, I was a bit hasty there.In my example it should have returned HR3 and HR4 OR HR5.Sun, 14 Oct 2012 05:14:37 GMTDon.http://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]Don. (10/13/2012)[/b][hr]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 [code="sql"] 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 ALLSELECT 'C2', 'Beta Ltd', 'Joe Soap', 'CR2'INSERT INTO #HISTORY (ACCOUNTNO, LASTUSER, LASTDATE, RECID)SELECT 'C1', 'JOE', '20120101', 'HR1' UNION ALLSELECT 'C1', 'BOB', '20120101', 'HR2' UNION ALLSELECT 'C1', 'JOE', '20120201', 'HR3' UNION ALLSELECT 'C2', 'JOE', '20120202', 'HR4' UNION ALLSELECT 'C2', 'BOB', '20120202', 'HR5'[/code]How would I get a History select script to return HR1 and HR4?Thanks[/quote][quote][b]Don. (10/12/2012)[/b][hr]I'd like to get the join the tables, but only show the most recent record in History.[/quote]Why HR1? HR3 is on the most recent row. Assuming you mean HR3, then this works against the sample data set;[code="sql"]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) hON h.Accountno = c.Accountno[/code]Sun, 14 Oct 2012 02:53:28 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]Don. (10/13/2012)[/b][hr]Hi Guys,INSERT INTO #HISTORY (ACCOUNTNO, LASTUSER, LASTDATE, RECID)SELECT 'C1', 'JOE', '20120101', 'HR1' UNION ALLSELECT 'C1', 'BOB', '20120101', 'HR2' UNION ALLSELECT 'C1', 'JOE', '20120201', 'HR3' UNION ALLSELECT 'C2', 'JOE', '20120202', 'HR4' UNION ALLSELECT 'C2', 'BOB', '20120202', 'HR5'[/code]How would I get a History select script to return HR1 and HR4?[/quote]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.Sat, 13 Oct 2012 19:19:52 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspxHi 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 [code="sql"] 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 ALLSELECT 'C2', 'Beta Ltd', 'Joe Soap', 'CR2'INSERT INTO #HISTORY (ACCOUNTNO, LASTUSER, LASTDATE, RECID)SELECT 'C1', 'JOE', '20120101', 'HR1' UNION ALLSELECT 'C1', 'BOB', '20120101', 'HR2' UNION ALLSELECT 'C1', 'JOE', '20120201', 'HR3' UNION ALLSELECT 'C2', 'JOE', '20120202', 'HR4' UNION ALLSELECT 'C2', 'BOB', '20120202', 'HR5'[/code]How would I get a History select script to return HR1 and HR4?ThanksSat, 13 Oct 2012 16:15:59 GMTDon.http://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]Don. (10/12/2012)[/b][hr]Chris that looks almost perfect, thank you. (Needed x instead of h in the initial select)[code="sql"]SELECT c.*, [b]x[/b].*FROM Contact cOUTER APPLY ( SELECT TOP 1 h.* FROM History h WHERE h.Accountno = c.Accountno ORDER BY h.LastDate DESC) x[/code]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 :( )?[/quote]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.[code="sql"]SELECT c.Accountno, C.Company, C.Contact, C.Recid, H.lastuser, H.lastdateFROM contact CLEFT JOIN ( SELECT accountno, MAX(lastdate) AS lastdate FROM history GROUP BY accountno) mx ON mx.accountno = h.accountnoLEFT JOIN history h ON h.accountno = mx.accountno AND h.lastdate = mx.lastdate[/code]Edit: having said that, I'd advocate Tom's approach as sound, sensible defensive coding.Sat, 13 Oct 2012 14:30:09 GMTChrisM@homehttp://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspxIf I've understood correctly what is wanted, it could be[code]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[/code]Sat, 13 Oct 2012 14:10:43 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspxHow 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?[code="sql"]SELECT accountno,MAX(lastdate) AS lastdateFROM historyGROUP BY accountno[/code]If I had the recids of the history records, I could use them as in the script below...[code="sql"]SELECT c.Accountno,C.Company, C.Contact,C.Recid,H.lastuser,H.lastdateFROM contact CLEFT JOIN history h ON C.accountno=h.accountno [b]and h.RECID IN (....[/b][/code]Any suggestions would be greatly appreciated.Sat, 13 Oct 2012 04:32:13 GMTDon.http://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]DiverKas (10/12/2012)[/b][hr]For older environments or other than T-SQL flavor, more generically would be something like (untested):[code="sql"]SELECT c.Accountno,C.Company,C.Contact,C.Recid,H.lastuser,H.lastdateFROM contact CLEFT JOIN history h ON C.accountno=h.accountnoLEFT JOIN (SELECT accountno,MAX(lastdate) AS lastdate FROM history GROUP BY accountno) mx ON h.accountno=mx.accountno AND h.lastdate=mx.lastdate[/code][/quote]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.Fri, 12 Oct 2012 15:12:13 GMTDon.http://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspxFor older environments or other than T-SQL flavor, more generically would be something like (untested):[code="sql"]SELECT c.Accountno,C.Company,C.Contact,C.Recid,H.lastuser,H.lastdateFROM contact CLEFT JOIN history h ON C.accountno=h.accountnoLEFT JOIN (SELECT accountno,MAX(lastdate) AS lastdate FROM history GROUP BY accountno) mx ON h.accountno=mx.accountno AND h.lastdate=mx.lastdate[/code]Fri, 12 Oct 2012 11:45:51 GMTDiverKashttp://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspxChris that looks almost perfect, thank you. (Needed x instead of h in the initial select)[code="sql"]SELECT c.*, [b]x[/b].*FROM Contact cOUTER APPLY ( SELECT TOP 1 h.* FROM History h WHERE h.Accountno = c.Accountno ORDER BY h.LastDate DESC) x[/code]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 :( )?Fri, 12 Oct 2012 10:31:08 GMTDon.http://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspx[quote][b]Don. (10/12/2012)[/b][hr]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?ContactAccountno Company, Contact, RecidHistoryAccountno, LastUser, LastDate, RecidAccountno is specific to each contact.Recid is unique to each recordI hope this isnt to to vagueThanks for your help.[/quote][code="sql"]SELECT c.*, h.*FROM Contact cOUTER APPLY ( SELECT TOP 1 h.* FROM History h WHERE h.Accountno = c.Accountno ORDER BY h.LastDate DESC) x[/code]Fri, 12 Oct 2012 09:57:44 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1372219-391-1.aspxEssentially 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?ContactAccountno Company, Contact, RecidHistoryAccountno, LastUser, LastDate, RecidAccountno is specific to each contact.Recid is unique to each recordI hope this isnt to to vagueThanks for your help.Fri, 12 Oct 2012 09:16:58 GMTDon.