T-SQL, how to get top three maximum values and if repeats, include them?-CodePudding

As stated in the title, I wish to select the top 3 values, and if the value that is counted repeats, include it too.

SELECT b.planID, count(b.planID) AS PopularPlan
FROM dbo.Subscriber as b
GROUP BY B.planID
ORDER BY count(b.planID) DESC;

This output returns all of the maximum values, ex:

PlanID  PopularPlan
 101    2555
 123    2555
 432    2390
  23    2390
  45    2090
  12    2080
  55    2090

If I were to use TOP 3, to choose TOP 3, with the following SQL:

SELECT TOP 3 b.planID, count(b.planID) AS PopularPlan
FROM dbo.Subscriber as b
GROUP BY B.planID
ORDER BY count(b.planID) DESC;

It would return the following:

PlanID  PopularPlan
 101    2555
 123    2555
 432    2390

The desired output in this situation would be to do what I want is:

PlanID  PopularPlan
     101    2555
     123    2555
     432    2390
      23    2390
      45    2090

I understand that the TOP 3 limits to only three results, but I cannot seem to figure out an approach how to add if it is repeated.

Furthermore, if I were to include WITH TIES:

SELECT TOP 3 WITH TIES b.planID, count(b.planID) AS PopularPlan
FROM dbo.Subscriber as b
GROUP BY B.planID
ORDER BY count(b.planID) DESC;

The output in this case is:

PlanID  PopularPlan
     101    2555
     123    2555
     432    2390
      23    2390

Upon testing, if I choose TOP 5 the 2090 value appears, but I wish to do the same with TOP 3 if possible so that the output is as-is:

PlanID  PopularPlan
     101    2555
     123    2555
     432    2390
      23    2390
      45    2090 <- has to have this value too

CodePudding user response：

DENSE_RANK() is your friend:

WITH cteOriginal AS
(
    SELECT b.planID, count(b.planID) AS PopularPlan
    FROM dbo.Subscriber as b
    GROUP BY B.planID
)
, cteDenseRank AS
(
    SELECT *, DENSE_RANK() OVER( ORDER BY PopularPlan DESC) AS DRank
    FROM cteOriginal
)
SELECT planID, PopularPlan
FROM cteDenseRank
WHERE DRank <= 3
ORDER BY PopularPlan DESC
;

Presumably you could merge the two CTEs together, but I am never sure exactly how the OVER functions interact with GROUP BY, so I did it this slightly longer way.

Personally I can say that since ROW_NUMBER(), RANK() and DENSE_RANK() windowing functions came out in T-SQL (2008?) I hardly ever use TOP any more.

Edited - the DENSE_RANK() To show the top results.