Joining to a table conditionally by Index and/or ID-CodePudding

This table represents a series of document reprints. For example, IdentifierBySet 1054 has been printed 8 times, and 149141 has only been printed once. I need to join to the table and get the parent DocumentID which in the case of IdentifierBySet 1054 being printed for the 8th time, the parent DocumentID is the value of DocumentID at IndexBySet 7, i.e. the preceding IndexBySet from the IndexBySet value returned from the join. In the case of IdentifierBySet 149141, it only has one value, so the parent DocumentID is the DocumentID at IndexBySet 1. How do I make this join and return the required value conditionally?

DECLARE @tbl TABLE
(IdentifierBySet varchar(10), IndexBySet int, DocumentID UNIQUEIDENTIFIER)

INSERT INTO @tbl
SELECT 1054, 1, 'FF5A398E-F6D7-4A8D-97B2-FBF1E6ED095B'
UNION
SELECT 1054, 2, 'B8A08EE1-302C-4865-AD9A-1AA559597F86'
UNION
SELECT 1054, 3, '7C7D7FC0-7C41-4283-936C-36599FADF695'
UNION
SELECT 1054, 4, '6F53B845-2107-4757-99EC-56B9DD76E300'
UNION
SELECT 1054, 5, '9E0CC5CE-1B7D-4D19-AFE7-E7849843CC2A'
UNION
SELECT 1054, 6, '6209881F-EDDE-4E69-955E-B29BE9913A2E'
UNION
SELECT 1054, 7, 'D0C5C822-B53B-4BA0-8923-6662F08FCF8A'
UNION
SELECT 1054, 8, '0A0C1281-B130-4E98-9551-5E64731D91DB'
UNION
SELECT 149141, 1, '041908FD-7B8B-4542-8F1A-1E453DE9D5BE'
UNION
SELECT 171251, 1, '65AB2C7B-B569-4E18-B53E-F6E15179990B'
UNION
SELECT 171251, 2, '0D245369-3C8D-4490-9708-214D5BAA913A'

SELECT * FROM @tbl

IdentifierBySet IndexBySet  DocumentID
1054            1           FF5A398E-F6D7-4A8D-97B2-FBF1E6ED095B
1054            2           B8A08EE1-302C-4865-AD9A-1AA559597F86
1054            3           7C7D7FC0-7C41-4283-936C-36599FADF695
1054            4           6F53B845-2107-4757-99EC-56B9DD76E300
1054            5           9E0CC5CE-1B7D-4D19-AFE7-E7849843CC2A
1054            6           6209881F-EDDE-4E69-955E-B29BE9913A2E
1054            7           D0C5C822-B53B-4BA0-8923-6662F08FCF8A
1054            8           0A0C1281-B130-4E98-9551-5E64731D91DB
149141          1           041908FD-7B8B-4542-8F1A-1E453DE9D5BE
171251          1           65AB2C7B-B569-4E18-B53E-F6E15179990B
171251          2           0D245369-3C8D-4490-9708-214D5BAA913A

Expected results

IdentifierBySet IndexBySet  DocumentID
1054            7           D0C5C822-B53B-4BA0-8923-6662F08FCF8A
149141          1           041908FD-7B8B-4542-8F1A-1E453DE9D5BE
171251          1           65AB2C7B-B569-4E18-B53E-F6E15179990B

CodePudding user response：

Row_number(), take 2 last, take first of them

select top(1) with ties *
from (
   select *, row_number() over(partition by IdentifierBySet order by IndexBySet desc) rn
   from @tbl ) t
where rn <=2
order by row_number() over(partition by IdentifierBySet order by rn desc)