Selecting paged joined rows to a reliable limit of distinct related rows

Question

Consider a SQL Server 2017 database with four tables; A, B, C and D.

Table D references C with a foreign key constraint, as does C to B, and B to A.

The intent is to INNER JOIN each table together, selecting all rows in D which satisfy certain criteria, but only up to the point at which a defined number n of rows in table A have been joined to.

This should be page-able so a subsequent query can (with a defined offset) have its result set of D rows join indirectly to the next set of n table A rows.

Note that it is common for not all (or even most) of table A's rows to not have one more valid relationship to a row in table D. Therefore using a basic SELECT DISTINCT TOP n would not provide a result set of table D rows with a reliable number of distinct table A relationships.

Answer 1

use Window Function for this

SELECT
*
FROM
(
SELECT
    *, dense_rank() over (order by A.ID) AS R
FROM
    A
    INNER JOIN B ON B.ID_A=A.ID
    INNER JOIN C ON C.ID_B=B.ID
    INNER JOIN D ON D.ID_C=C.ID
) AS RES
WHERE
RES.R<=PUT_LIMIT_OF_A_HERE
PUT_LIMITS_OF_ROWS_HERE