CREATE TABLE [dbo].[TargettingReport](
    [MLISNPI] [varchar](50) NULL,
    [CLIENT_ID1] [varchar](50) NULL,
    [Sum of count] [int] NULL
) ON [PRIMARY]

select [MLISNPI],[CLIENT_ID1],MAX([Sum of count])
  from [TargettingReport]
  group by
  [MLISNPI],[CLIENT_ID1]

+---------+------------+--------------+
| MLISNPI | CLIENT_ID1 | sum of count |
+---------+------------+--------------+
|  567890 |     214060 |           18 |
|  678901 |     214060 |           58 |
|  789012 |     214060 |           27 |
|  891012 |     214060 |            1 |
|  101112 |     214060 |           23 |
|  003001 |     101596 |            0 |
|  003001 |     101522 |          436 |
|  003001 |     101597 |            0 |
|  003002 |     102165 |           66 |
|  003002 |     100062 |            1 |
|  003002 |     211074 |         1229 |
|  003006 |     102235 |           21 |
|  003014 |     213926 |            5 |
|  003016 |     213143 |            3 |
|  003023 |     213801 |           55 |
|  003023 |     212876 |           44 |
|  003023 |     100218 |            0 |
|  003028 |     211144 |          133 |
|  003041 |     100236 |          346 |
|  003041 |     103164 |           65 |
|  003051 |     213402 |          157 |
|  003058 |     100572 |           28 |
|  003065 |     101632 |           29 |
|  003071 |     213632 |            6 |
|  003072 |     101506 |            4 |
|  003081 |     100087 |          398 |
|  003083 |     214311 |            7 |
|  003117 |     210178 |          203 |
|  003121 |     214008 |            9 |
|  003139 |     102179 |         1635 |
|  003147 |     216022 |           21 |
|  003149 |     211425 |            1 |
|  003186 |     215748 |            1 |
+---------+------------+--------------+

; with numbered as
(
  select *,
      -- Assign number to each record
      -- Numbering is separate for each pair of MLISNPI and CLIENT_ID1
      -- Highest [Sum of desc] will get number 1 and so forth
         row_number() over (partition by [MLISNPI], [CLIENT_ID1]
                            order by [Sum of count] desc) rn
    from [TargettingReport]
)
select [MLISNPI],
       [CLIENT_ID1],
       [Sum of count]
  from numbered
-- Now one can filter ranks
 where rn between 2 and 3

; with numbered as
(
  select mlisnpi,
         [sum of count],
      -- Assign number to each record
      -- Numbering is separate for each MLISNPI
      -- Highest [Sum of desc] will get number 1 and so forth
         row_number() over (partition by [MLISNPI]
                            order by [Sum of count] desc) rn
    from [TargettingReport]
)
select mlisnpi,
       [1] Rank1,
       [2] Rank2,
       [3] Rank3
  from numbered
 pivot (max([Sum of count]) for rn in ([1], [2], [3])) p

; with numbered as
(
  select *,
         row_number() over (partition by [MLISNPI]
                            order by [Sum of count] desc) rn
    from [TargettingReport]
),
unioned as
(
  select mlisnpi,
         convert(varchar(20), [Sum of count]) value,
         rn * 2 rn
    from numbered
   union all
   select mlisnpi,
       convert(varchar(20), [Client_ID1]),
       rn * 2 - 1
    from numbered
)
select mlisnpi,
       [1] Client1,
       [2] Rank1,
       [3] Client2,
       [4] Rank2,
       [5] Client3,
       [6] Rank3
  from unioned
 pivot (max(value) for rn in ([1], [2], [3], [4], [5], [6])) p

select
    *
from
(
    select
        [mlisnpi],
        [client_id1],
        row_number() over (order by [sum of count] desc) as rownumber
    from [targettingreport]
    group by
        [mlisnpi],
        [client_id1],
        [sum of count]
) f
where
    rownumber between 2 and 3

CREATE VIEW TESTVIEW

AS

SELECT ABB4.[MLISNPI], ABB4.[CLIENT_ID1], MAX(ABB4.[SUM OF COUNT]) AS NEW3
FROM TARGETTINGREPORT AS ABB4
    LEFT JOIN (SELECT ABB2.[MLISNPI], ABB2.[CLIENT_ID1], MAX([SUM OF COUNT]) AS NEW2
                FROM TARGETTINGREPORT AS ABB2
                LEFT JOIN (SELECT [MLISNPI], [CLIENT_ID1], MAX([SUM OF COUNT]) AS NEW1
                            FROM TARGETTINGREPORT GROUP BY MLISNPI, [CLIENT_ID1])
                AS ABB1 ON ABB1.MLISNPI = ABB2.MLISNPI AND ABB1.NEW1 = ABB2.[SUM OF COUNT] and ABB1.CLIENT_ID1 = ABB2.CLIENT_ID1
                WHERE ABB1.MLISNPI IS NULL
                GROUP BY ABB2.MLISNPI, ABB2.[CLIENT_ID1])
     AS ABB3 ON ABB3.MLISNPI = ABB4.MLISNPI AND ABB3.NEW2 = ABB4.[SUM OF COUNT] AND ABB3.CLIENT_ID1 = ABB4.CLIENT_ID1
     LEFT JOIN (SELECT ABB5.[MLISNPI], ABB5.[CLIENT_ID1], MAX([SUM OF COUNT]) AS NEW4
                FROM TARGETTINGREPORT AS ABB5
                GROUP BY ABB5.MLISNPI, ABB5.[CLIENT_ID1])
                AS ABB6 ON ABB6.MLISNPI = ABB4.MLISNPI AND ABB6.NEW4 = ABB4.[SUM OF COUNT] AND ABB6.CLIENT_ID1 = ABB4.CLIENT_ID1
WHERE ABB3.MLISNPI IS NULL AND ABB6.MLISNPI IS NULL
GROUP BY ABB4.MLISNPI, ABB4.[CLIENT_ID1]

UNION ALL

SELECT ABB2.[MLISNPI], ABB2.[CLIENT_ID1], MAX([SUM OF COUNT]) AS NEW2
FROM TARGETTINGREPORT AS ABB2
    LEFT JOIN (SELECT [MLISNPI], [CLIENT_ID1], MAX([SUM OF COUNT]) AS NEW1
                FROM TARGETTINGREPORT GROUP BY MLISNPI, [CLIENT_ID1])
                AS ABB1 ON ABB1.MLISNPI = ABB2.MLISNPI AND ABB1.NEW1 = ABB2.[SUM OF COUNT] AND ABB1.CLIENT_ID1 = ABB2.CLIENT_ID1
WHERE ABB1.MLISNPI IS NULL
GROUP BY ABB2.MLISNPI, ABB2.[CLIENT_ID1]

UNION ALL

SELECT [MLISNPI], [CLIENT_ID1], MAX([SUM OF COUNT]) AS NEW1
FROM TARGETTINGREPORT
GROUP BY MLISNPI, [CLIENT_ID1]

CREATE VIEW TESTVIEW2

AS

SELECT ABB1.MLISNPI, ABB1.CLIENT_ID1, ABB1.NEW3
FROM TESTVIEW AS ABB1
    LEFT JOIN (SELECT MLISNPI, CLIENT_ID1, MIN(NEW3) AS VAR1
                FROM TESTVIEW
                GROUP BY MLISNPI, CLIENT_ID1) AS ABB2
    ON ABB2.MLISNPI = ABB1.MLISNPI AND ABB2.CLIENT_ID1 = ABB1.CLIENT_ID1 AND ABB2.VAR1 = ABB1.NEW3
    LEFT JOIN (SELECT MLISNPI, CLIENT_ID1, MAX(NEW3) AS VAR2
                FROM TESTVIEW
                GROUP BY MLISNPI, CLIENT_ID1) AS ABB3
    ON ABB3.MLISNPI = ABB1.MLISNPI AND ABB3.CLIENT_ID1 = ABB1.CLIENT_ID1 AND ABB3.VAR2 = ABB1.NEW3
WHERE ABB2.MLISNPI IS NULL AND ABB3.MLISNPI IS NULL

SELECT TESTVIEW.MLISNPI, TESTVIEW.CLIENT_ID1, MAX(TESTVIEW.NEW3) AS '1', TESTVIEW2.NEW3 AS '2', MIN(TESTVIEW.NEW3) AS '3'
FROM TESTVIEW
    LEFT JOIN TESTVIEW2 ON TESTVIEW2.MLISNPI = TESTVIEW.MLISNPI AND TESTVIEW2.CLIENT_ID1 = TESTVIEW.CLIENT_ID1
GROUP BY TESTVIEW.MLISNPI, TESTVIEW.CLIENT_ID1, TESTVIEW2.NEW3

select top 3 [MLISNPI],[CLIENT_ID1], Max([Sum of count]) COUNTS
from [TargettingReport]
group by
[MLISNPI],[CLIENT_ID1]
ORDER by [Sum of count] desc