CREATE TABLE #t (
	GRP INT
	,ID BIGINT
	,N INT
	,DTA DATE
	,isAсtive BIT
	,isInvalid BIT
	,isOverlapped BIT
	)

insert into #t Values 
(1, 1, 1, '20180101', 0, 0, 1),
(1, 1, 2, '20180102', 0, 1, 1),
(1, 1, 3, '20180103', 0, 0, 1),
(1, 1, 4, '20180110', 1, 0, 0),
(1, 2, 1, '20180101', 0, 0, 1),
(1, 2, 2, '20180102', 1, 1, 1),
(1, 2, 3, '20180102', 0, 0, 1),
(1, 3, 1, '20180105', 1, 0, 0),
(2, 2, 1, '20180101', 0, 0, 1),
(2, 2, 2, '20180102', 0, 1, 1),
(2, 2, 3, '20180102', 0, 0, 1),
(2, 3, 4, '20180103', 1, 0, 0)


Select top (1) with ties *
from #t
Where DTA between '20180101' and '20180103' and isAсtive = 1
Order by ROW_NUMBER() over (partition by GRP, ID order by N DESC) ASC

create index A1 on #t (DTA)  where  isAсtive = 1;

create index A0 on #t (DTA)  where  isAсtive = 0;

CREATE INDEX ix ON #t (GRP, ID, N DESC)
    --INCLUDE ()
    WHERE isAсtive = 1

SELECT *
FROM (
    SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY GRP, ID ORDER BY N DESC)
    FROM #t
    WHERE DTA BETWEEN '20180101' AND '20180103'
        AND isAсtive = 1
) t
WHERE t.rn = 1

CREATE INDEX ix1 ON #t (DTA)
    INCLUDE (GRP)
    WHERE isAсtive = 1

CREATE INDEX ix2 ON #t (GRP, ID, N DESC)
    INCLUDE (DTA)
    WHERE isAсtive = 1

DECLARE @GRP_Start INT
      , @GRP_End INT

SELECT @GRP_Start = MIN(GRP)
     , @GRP_End = MAX(GRP)
FROM #t WITH(INDEX(ix1))
WHERE DTA BETWEEN '20180101' AND '20180103'
    AND isAсtive = 1

SELECT GRP, ID, N, DTA
FROM (
    SELECT GRP, ID, N, DTA, rn = ROW_NUMBER() OVER (PARTITION BY GRP, ID ORDER BY N DESC)
    FROM #t WITH(INDEX(ix2))
    WHERE DTA BETWEEN '20180101' AND '20180103'
        AND isAсtive = 1
        AND GRP BETWEEN @GRP_Start AND @GRP_End
) t
WHERE t.rn = 1
OPTION(RECOMPILE)