Flexable/Effective means to join the Nth record

  • I have no doubt this has been covered many times and has many answers but I'm hoping by now this has been refined down to where you can get not just teh 1st or last record but the Nth record.

    Let say I has a join like the below and I want just teh first or just the last record form the PERSON_HISTORY table which will have 1 or more rows of data per person.  To get the 1st or last I could do a 2nd join to PERSON_HISTORY via subquery and use min or max to return teh first or last row of data.   Is there a better way to do this and is there a better way that also let's you specify teh Nth record so you could get the 2nd or 3rd row of data versus just the first or last?

    --Get first record from PERSON_HISTORY table
    SELECT P.sName, PH.*
    FROM PERSON PO JOIN PERSON_HISTORY PH ON P.hID = PH.hPerson
    JOIN ( SELECT Min(hID) AS 'hID', hPerson
    FROM PERSON
    GROUP BY hPerson
    ) PX ON PH.hID = PX.hID

     

    Kindest Regards,

    Just say No to Facebook!
  • Typically that's done with a cte that uses ROW_NUMBER().  Easy for first/specific row num, but for last to be easy, you'd have to do a DESC ORDER BY in the ROW_NUMBER().

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Roughly:

    ;WITH cte_person AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY hPerson ORDER BY hID) AS row_num,
    ROW_NUMBER() OVER(PARTITION BY hPerson ORDER BY hID DESC) AS row_num_desc
    FROM dbo.PERSON
    )
    SELECT ...
    FROM cte_person
    INNER JOIN ...
    WHERE row_num = 1 /*first*/ OR row_num_desc = 1 /*last*/ /* or row_num IN (2, 3) */

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thanks Scott

    Kindest Regards,

    Just say No to Facebook!
  • Scott - a follow-up now that I've made this work.  so if the PERSOIN_HISTORY table had 5 rows for the same hPerson and I used teh criteria

    AND row_num_desc = 2

    I'm saying show me the next to the last row for that person from the PERSON_HISTORY table correct?

    • This reply was modified 1 month, 1 week ago by  YSLGuru.

    Kindest Regards,

    Just say No to Facebook!
  • Yes, that is correct.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Maybe you could try OFFSET and FETCH NEXT on the righthand side of OUTER APPLY

    declare
    @nth_from_top int=2,
    @nth_from_bot int=1;

    with
    person as (
    select 'Jovi' sName, 1 hID union all
    select 'Kane' sName, 2 hID union all
    select 'Xane' sName, 3 hID),
    person_history as (
    select 'Joxe' sName, 1 hPerson, getdate() dt union all
    select 'Jaye' sName, 1 hPerson, getdate()-1 union all
    select 'Jaxo' sName, 1 hPerson, getdate()-2 union all
    select 'Kade' sName, 2 hPerson, getdate() union all
    select 'Kale' sName, 2 hPerson, getdate()-1 union all
    select 'Kate' sName, 2 hPerson, getdate()-2 union all
    select 'Kake' sName, 2 hPerson, getdate()-3 union all
    select 'Kaye' sName, 2 hPerson, getdate()-4 union all
    select 'Xade' sName, 3 hPerson, getdate() union all
    select 'Xale' sName, 3 hPerson, getdate()-1 union all
    select 'Xate' sName, 3 hPerson, getdate()-2 union all
    select 'Xaqe' sName, 3 hPerson, getdate()-3)
    select concat(@nth_from_top, ' from top') nth_sort_order, p.*, oa.*
    from person p
    outer apply (select ph.sName
    from person_history ph
    where ph.hPerson=p.hID
    order by ph.dt desc
    offset @nth_from_top rows
    fetch next 1 rows only) oa(sName)
    union all
    select concat(@nth_from_bot, ' from bottom') nth_sort_order, p.*, oa.*
    from person p
    outer apply (select ph.sName
    from person_history ph
    where ph.hPerson=p.hID
    order by ph.dt asc
    offset @nth_from_bot rows
    fetch next 1 rows only) oa(sName);

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Scott - Do you know of a way to incorporate Count(hID) in there to get a count of the total number of rows per hPerson ? I tried it using Count(hID) OVER(Partition by hPerson ORDER BY hID and that works but only when row-num_desc  = 1.  If an hPerson has 10 rows in the PERSON_HISTORY table  then when row_num = 1 the value of count is 1 and when row_num_desc = 109 the Count = 109.  So basically count is teh same value as row_num; anyway to separate those?

    Kindest Regards,

    Just say No to Facebook!
  • Steve - Thanks for adding to the discussion but I think I like Scott's simpler version on how to tackle this.

    Kindest Regards,

    Just say No to Facebook!
  • We could brute force it; I'll try to think of a better way when I get a chance:

    ;WITH cte_person AS (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY hPerson ORDER BY hID) AS row_num,
    (SELECT COUNT(*) FROM dbo.PERSON P2 WHERE P2.hPerson = P1.hPerson) AS total_count, --<<--
    ROW_NUMBER() OVER(PARTITION BY hPerson ORDER BY hID DESC) AS row_num_desc
    FROM dbo.PERSON P1
    )

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Scott - No worries, anytime you can provide some help it is much appreciated, same goes for all who take the time to reply.

    Kindest Regards,

    Just say No to Facebook!
  • The above general approach should work, there just may be a more efficient way that's not popping into my head right now.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Not sure why your COUNT didn't work - this works just fine:

    DECLARE @person TABLE (PersonID int PRIMARY KEY CLUSTERED, sName varchar(30));
    INSERT INTO @person (PersonID, sName)
    VALUES (1, 'aName6')
    , (2, 'bName7')
    , (3, 'cName10')
    , (4, 'dName1');

    DECLARE @person_history TABLE (PersonID int, HistoryDate date, sName varchar(30));
    INSERT INTO @person_history (PersonID, HistoryDate, sName)
    VALUES (1, getdate() - 1, 'aName5')
    , (1, getdate() - 2, 'aName4')
    , (1, getdate() - 3, 'aName3')
    , (1, getdate() - 4, 'aName2')
    , (1, getdate() - 5, 'aName1')
    , (2, getdate() - 1, 'bName6')
    , (2, getdate() - 2, 'bName5')
    , (2, getdate() - 3, 'bName4')
    , (2, getdate() - 4, 'bName3')
    , (2, getdate() - 5, 'bName2')
    , (2, getdate() - 6, 'bName1')
    , (3, getdate() - 1, 'cName9')
    , (3, getdate() - 2, 'cName8')
    , (3, getdate() - 3, 'cName7')
    , (3, getdate() - 4, 'cName6')
    , (3, getdate() - 5, 'cName5')
    , (3, getdate() - 6, 'cName4')
    , (3, getdate() - 7, 'cName3')
    , (3, getdate() - 8, 'cName2')
    , (3, getdate() - 9, 'cName1');

    DECLARE @nth_row int = 1
    , @row_order bit = 0;

    WITH person_rank
    AS (
    SELECT p.PersonID
    , CurrentName = p.sName
    , ph.HistoryDate
    , PreviousName = ph.sName
    , TotalHistory = count(*) OVER(PARTITION BY ph.PersonID)
    , rn = row_number() OVER(PARTITION BY ph.PersonID ORDER BY ph.HistoryDate)
    FROM @person p
    LEFT JOIN @person_history ph ON ph.PersonID = p.PersonID
    )
    SELECT pr.PersonID
    , pr.CurrentName
    , pr.HistoryDate
    , pr.PreviousName
    , pr.TotalHistory
    FROM person_rank pr
    WHERE pr.rn = iif(@row_order = 0, pr.TotalHistory - @nth_row + 1, @nth_row);

    I will also say - you have been here long enough to know that you should provide sample data and expected results.  You don't need both ASC and DESC row numbers - you can calculate the descending from the total history rows.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 13 posts - 1 through 12 (of 12 total)

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