CODEWITHSUNDEEP
sqlsql-server
wecing
wecing

Reputation: 534

Get all groups that have the maximum count

Suppose now I have a table T which is like this:

A|B
1|1 
1|2 
2|1 
2|2 
2|3 
3|1 
3|2 
3|3

With select A, count(*) from T group by A we should get:

A|count(*)
1|2
2|3
3|3

Now I need to get the last two rows in the previous query -- which have the maximum value of count(*). How can I do this in a single query?

Thanks.

Upvotes: 0

Views: 100

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

;WITH a(A,C,R) AS
(
  SELECT A, COUNT(*), RANK() OVER (ORDER BY COUNT(*) DESC)
  FROM dbo.T GROUP BY A
)
SELECT A, C FROM a WHERE R = 1;

Upvotes: 1

John Woo
John Woo

Reputation: 263713

WITH recordList
AS
(
    SELECT  A, COUNT(*) totalCount,
            DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) rn
    FROM    tableName
    GROUP BY A
)
SELECT  A, totalCount
FROM recordLIst
WHERE rn = 1

Upvotes: 3

Related Questions