June 14, 2024 at 5:46 pm
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!June 14, 2024 at 5:56 pm
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".
June 14, 2024 at 6:00 pm
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".
June 14, 2024 at 6:29 pm
Thanks Scott
Kindest Regards,
Just say No to Facebook!June 14, 2024 at 6:39 pm
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?
Kindest Regards,
Just say No to Facebook!June 15, 2024 at 5:50 am
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".
June 15, 2024 at 11:48 am
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
June 21, 2024 at 5:59 pm
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!June 21, 2024 at 6:00 pm
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!June 21, 2024 at 7:05 pm
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".
June 21, 2024 at 7:20 pm
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!June 21, 2024 at 8:44 pm
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".
June 22, 2024 at 5:01 pm
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