Get Top 2 highest value department wise in SQL

 We have used correlation sub query to achieve get top 2 highest salary department wise.

CREATE TABLE #Client(ClientId int, ClientName VARCHAR(35))
CREATE TABLE #Account(AccountId int, ClientId int, AccountValue numeric(18,2))

INSERT INTO #Client VALUES(1, 'Harshad')
INSERT INTO #Client VALUES(2, 'Krishna')
INSERT INTO #Client VALUES(3, 'Vinoth')

INSERT INTO #Account VALUES(1,1,100)
INSERT INTO #Account VALUES(2,1,200)
INSERT INTO #Account VALUES(3,1,300)
INSERT INTO #Account VALUES(4,2,100)
INSERT INTO #Account VALUES(5,2,400)
INSERT INTO #Account VALUES(6,2,500)

INSERT INTO #Account VALUES(7,3,700)
INSERT INTO #Account VALUES(8,3,800)
INSERT INTO #Account VALUES(9,3,400)
INSERT INTO #Account VALUES(10,3,500)

SELECT * FROM #Client
SELECT * FROM #Account

SELECT C.ClientName, A.AccountValue,A.ClientId,A.AccountId--a.accountid ,a.accountvalue  --a.accountid,c.clientid, C.ClientName, A.AccountValue
FROM #Client C
INNER JOIN #Account A
ON C.ClientId=A.ClientId
AND A.AccountID IN(
SELECT TOP 2 AccountId
FROM #Account D
WHERE D.ClientID=A.ClientId
ORDER BY D.AccountValue DESC)