How do I join tables, but select top 1 from 1-many tbl?

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


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

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

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • 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

    (

    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'

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

    ) S

    on Hi.ACCOUNTNO = S.ACCOUNTNO

    and Hi.RECID = S.Min_RecID

    ) 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

    ) S

    on Hi.ACCOUNTNO = S.ACCOUNTNO

    and Hi.RECID = S.Min_RecID

    ) 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 😎
    I want to win the lotto πŸ˜€
    I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

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

  • 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)

  • Don. (10/28/2012)


    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)

    Thanks for the feedback, Don. The script in bold - is it the 2000-compatible one?


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • ChrisM@home (10/28/2012)


    Thanks for the feedback, Don. The script in bold - is it the 2000-compatible one?

    Morning Chris,

    It was the following script you gave me help with:

    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

    I plugged it into my existing script and got the results needed.

  • Don. (10/28/2012)


    ChrisM@home (10/28/2012)


    Thanks for the feedback, Don. The script in bold - is it the 2000-compatible one?

    Morning Chris,

    It was the following script you gave me help with:

    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

    I plugged it into my existing script and got the results needed.

    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.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • I'll have a read.

    Thanks Chris.

  • Don.,

    Are you using a Touchstar system database?

  • Lee Crain (10/29/2012)


    Don.,

    Are you using a Touchstar system database?

    Hi Lee,

    Its for a CMS product called Goldmine.

  • Hi Don,

    Can you please check below query -

    SELECT c.*, h.*

    FROM #CONTACT c

    INNER JOIN (

    SELECT Accountno, ROW_NUMBER() OVER(PARTITION BY accountno ORDER BY lastdate) Rownum ,RECID FROM #History

    GROUP BY Accountno,LastDate,RECID) h

    ON c.ACCOUNTNO=h.ACCOUNTNO

    WHERE h.Rownum=1

  • chandan.kumar (10/30/2012)


    Hi Don,

    Can you please check below query -

    SELECT c.*, h.*

    FROM #CONTACT c

    INNER JOIN (

    SELECT Accountno, ROW_NUMBER() OVER(PARTITION BY accountno ORDER BY lastdate) Rownum ,RECID FROM #History

    GROUP BY Accountno,LastDate,RECID) h

    ON c.ACCOUNTNO=h.ACCOUNTNO

    WHERE h.Rownum=1

    Always read the whole thread. It's a spec.

    Don. (10/12/2012)


    Any suggestions as to how Id be able to get the same results except using scripts for an older backend( SQL 2000 πŸ™ )?

    β€œ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

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply