SQL COALESCE & Top N within Group By

CREATE TABLE EMPLOYEE(
[EMPLOYEEID] INT PRIMARY KEY,
[NAME] NVARCHAR(50),
[MANAGERID] INT
)

GO

INSERT INTO EMPLOYEE VALUES(101,'Mary',102)
INSERT INTO EMPLOYEE VALUES(102,'Ravi',NULL)
INSERT INTO EMPLOYEE VALUES(103,'Raj',102)
INSERT INTO EMPLOYEE VALUES(104,'Pete',103)
INSERT INTO EMPLOYEE VALUES(105,'Prasad',103)
INSERT INTO EMPLOYEE VALUES(106,'Ben',103)

GO

SELECT E1.[NAME],COALESCE(E2.[NAME],'No Manager') AS [MANAGER NAME]
FROM EMPLOYEE E1
LEFT OUTER JOIN EMPLOYEE E2
ON E2.EMPLOYEEID =E1.MANAGERID


SQL Select Top N within Group By

drop table #test
create table #test (testid int identity(1,1), personid int, persondate int)

insert into #test (personid, persondate) values (1, 1)
insert into #test (personid, persondate) values (1, 2)
insert into #test (personid, persondate) values (1, 3)
insert into #test (personid, persondate) values (1, 4)
insert into #test (personid, persondate) values (1, 5)

insert into #test (personid, persondate) values (2, 1)
insert into #test (personid, persondate) values (2, 2)
insert into #test (personid, persondate) values (2, 3)
insert into #test (personid, persondate) values (2, 4)
insert into #test (personid, persondate) values (2, 5)

insert into #test (personid, persondate) values (3, 1)
insert into #test (personid, persondate) values (3, 2)
insert into #test (personid, persondate) values (3, 3)

insert into #test (personid, persondate) values (4, 1)
insert into #test (personid, persondate) values (4, 2)

SELECT * FROM #test

SELECT b.testid, b.personid, b.persondate, count(1) AS RowOrder
FROM #test a, #test b
WHERE a.persondate >= b.persondate AND a.personid = b.personid
GROUP BY b.testid, b.personid, B.persondate
HAVING Count(1) BETWEEN 1 AND 1
ORDER BY 1 DESC