CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsql-server-2008-r2
Michael Goldshteyn
Michael Goldshteyn

Reputation: 74360

SQL Server GROUP BY dilemma

I have a table with rows containing a column called MySubId. Values in this column can be duplicated. I want to find the MySubId value and the number of rows for the MySubId value that appears the most.

I have the following query:

SELECT MySubId, COUNT(MySubId) AS MySubIdCount
FROM MyTable
GROUP BY MySubId
HAVING COUNT(MySubId)=MAX(COUNT(MySubId))

But I get the error:

Msg 130, Level 15, State 1, Line 4 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Is it not possible to do this with a simple query? Must I embed a subquery to calculate the MAX before using it in the HAVING clause?

Update:

I see a lot of anwers filtering the result set by using TOP 1, so am I to assume that there is no way to use the MAX function to filter this query to just the max appearing MySubId values?

Upvotes: 2

Views: 186

Answers (4)

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

DECLARE @MyTable TABLE (
    MySubId INT
)

INSERT INTO @MyTable (MySubId) VALUES (1)
INSERT INTO @MyTable (MySubId) VALUES (1)
INSERT INTO @MyTable (MySubId) VALUES (1)
INSERT INTO @MyTable (MySubId) VALUES (2)
INSERT INTO @MyTable (MySubId) VALUES (2)
INSERT INTO @MyTable (MySubId) VALUES (2)
INSERT INTO @MyTable (MySubId) VALUES (2)
INSERT INTO @MyTable (MySubId) VALUES (3)
INSERT INTO @MyTable (MySubId) VALUES (3)
INSERT INTO @MyTable (MySubId) VALUES (3)
INSERT INTO @MyTable (MySubId) VALUES (3)

;WITH Counts_CTE (MySubId, MySubIdCount, RowNumber)
AS
(
    SELECT 
        MySubId, 
        COUNT(MySubId) AS MySubIdCount,
        DENSE_RANK() OVER (ORDER BY COUNT(MySubId) DESC) AS RowNumber
    FROM @MyTable
    GROUP BY MySubId
)
SELECT *
FROM Counts_CTE
WHERE RowNumber = 1

Upvotes: 0

SQLMason
SQLMason

Reputation: 3275

This is just an FYI as I believe this would be the next logical step for the poster

If you want to delete all duplicates starting with the Newest...

DECLARE @ROWCOUNT INT
SET @ROWCOUNT = 1

WHILE @ROWCOUNT > 0
BEGIN
     DELETE 
     FROM MyTable
     WHERE ID IN
     (
        SELECT MAX(ID)
        FROM MyTable
        GROUP By MySubID
        HAVING COUNT(1) > 1
     )
     SET @ROWCOUNT = @@ROWCOUNT
END

Upvotes: 0

Hogan
Hogan

Reputation: 70523

select top 1 MySubId, Count(MySubId)
from MyTable
group by MySubId
order by count(MySubId) DESC

Upvotes: 1

Ralph Shillington
Ralph Shillington

Reputation: 21098

select top 1 with ties MySubId, Count(MySubId) as MySubIdCount
from MyTable
group by MuSubId
order by 2 desc

Upvotes: 6

Related Questions