CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Alex Gordon
Alex Gordon

Reputation: 60691

selecting 2nd and 3rd highest numbers for a group

i have a table:

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

i am trying to select the 2nd and third highest SUM OF COUNT based on MLISNPI, CLIENT_ID1

this is how i get the max for every unique occurrence of MLISNPI, CLIENT_ID1:

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

the question is how do i get the 2nd and 3rd highest?

here's some example data:

+---------+------------+--------------+
| 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 |
+---------+------------+--------------+

Upvotes: 1

Views: 230

Answers (3)

Lloyd Banks
Lloyd Banks

Reputation: 36638

Create a view of the 3 top values

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]

Then create another view

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

And finally call the 3 top values in pivot form

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

Upvotes: 1

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

; 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

UPDATE: pivoting [Sum of count] around mlisnpi

; 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

Sql Fiddle is here.

In case you actually want Cliend_ID1 instead of [Sum of count], just replace all occurrences of [Sum of count] with Client_ID1. Don't add Client_ID1 to cte because pivot will include it in results and show each combination of mlisnpi and Client_ID1 in its own row.

UPDATE2: pivoting with title included. A bit more verbose, as you need to union all titles (client_id1) and manipulate row numbers to get correct mix of titles and sums. Also you need to convert all to the same data type (varchar here) to be able to use union all.

; 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

Sql Fiddle for this is here.

Upvotes: 4

Andrew Walters
Andrew Walters

Reputation: 4803

Can you do a where rownum < 3 and remove the max from your select?

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

Upvotes: 0

Related Questions