How to take make OFFSET & LIMIT with COUNT (OVER?) when having a subquery?
So it looks like to me what you are missing is the "Return only the top Created record for each instance".  So you are getting all rows, and then watever its top Created value is for the same SomeInfo record.  Unfortunately you can't just add the MAX(Created) = Created into the base WHERE clause.

If you just wrap the whole thing in a CTE you can then just add a MAX(Created) = Created into the WHERE and get what you are looking for (not that i think CTE's are the anwer for everything).

WITH CTE (ID, SomeInfo, GroupID, ParentID, Created, MaxCreated, RootCount, IsMulti)
AS
(
    SELECT
        Id,
        SomeInfo,
        GroupId,
        ParentId,
        Created,
        MAX(Created) OVER(PARTITION BY SomeInfo) AS MaxCreated,
        COUNT(Id) OVER(PARTITION BY SomeInfo) AS RootCount,
        CASE WHEN COUNT(Id) OVER(PARTITION BY SomeInfo) &gt; 1 THEN 1 ELSE 0 END AS IsMulti
    FROM
        Temp
)
SELECT ID, SomeInfo, GroupID, ParentID,  MaxCreated AS Created, RootCount, IsMulti
FROM CTE
WHERE
(
    GroupId = 1 
    AND ParentId IS NULL
    AND Created = MaxCreated
)
ORDER BY MaxCreated ASC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;


In my quick test it has the same execution plan and does not take any additional execution time (See execution plan added below). (now it is a small result set so it is probably something you will still need to test with.)



Hopefully that is more of what you are looking for.