Return MAX(COUNT(Column)) value
-
SET NOCOUNT ON
DECLARE @MyTable TABLE
(
[ID] INT,
[Name] VARCHAR(25)
)
INSERT @MyTable
SELECT 1, 'SomeName1_1' UNION ALL
SELECT 1, 'SomeName1_2' UNION ALL
SELECT 2, 'SomeName2_1' UNION ALL
SELECT 2, 'SomeName2_2' UNION ALL
SELECT 2, 'SomeName2_2' UNION ALL
SELECT 2, 'SomeName2_2' UNION ALL
SELECT 2, 'SomeName2_3' UNION ALL
SELECT 3, 'SomeName3_2' UNION ALL
SELECT 3, 'SomeName3_1' UNION ALL
SELECT 3, 'SomeName3_3' UNION ALL
SELECT 4, 'SomeName4_2' UNION ALL
SELECT 4, 'SomeName4_3' UNION ALL
SELECT 4, 'SomeName4_3' UNION ALL
SELECT 5, 'SomeName5_1' UNION ALL
SELECT 6, 'SomeName6_2' UNION ALL
SELECT 6, 'SomeName6_3'
/*
I had to do it with loop. Is it possible to get it set based.
Result needed Each ID should have only one row.
Name Should be the name which has MAX(COUNT(Name)) GROUP BY ID
IF MAX(COUNT(Name)) Group By id is same MIN(Name) is needed
Expected result
ID NAME
1 SomeName1_1
2 SomeName2_2
3 SomeName3_1
4 SomeName4_3
5 SomeName5_1
6 SomeName6_2
*/
Regards,
gova -
I don't believe this so for no one even read this post. No one like Remi is hanging around any more??
I did it like this. I would like to know if there is an easier way.
SELECT [ID], [Name], NameCount
INTO
#ErrNameCount
FROM
(
SELECT [ID], [Name], COUNT([Name]) NameCount
FROM
@MyTable
GROUP BY [ID], [Name]) A
SELECT A.[ID], MIN(A.[Name])
FROM
#ErrNameCount A
JOIN
(
SELECT [ID], MAX(NameCount) NameCount
FROM
#ErrNameCount
GROUP BY [ID] ) B
ON
A.[ID] = B.[ID]
AND A.NameCount = B.NameCount
GROUP BY A.[ID]
Regards,
gova -
So now I'm supposed to answer all messages under 10 minutes
.I don't think there's a much better way to do this unless you have access to CTE.
-
same result using CTE's
WITH ACTE ([ID],[Name],[NameCount]) AS
(
SELECT [ID], [Name], COUNT([Name]) NameCount FROM MyTable1 GROUP BY [ID], [Name]
 
SELECT B.[ID],MIN([NAME]) AS [NM]
FROM ACTE A INNER JOIN
(SELECT ID,MAX([NameCount]) AS MXCNT FROM ACTE GROUP BY ID ) B
ON A.ID=B.ID AND A.NAMECOUNT=B.MXCNT
GROUP BY B.ID
-
I have to wait for about an year (hope) to use CTE.
Remi 10 minutes took 4 days for you. I was wondering at that point no one even read the post.
Regards,
gova -
"
I don't believe this so for no one even read this post. No one like Remi is hanging around any more??
"
Was reffering to this... Took only 30 mins to answer your own post. Not always enough time to figure it out... even if you read it just as it is posted.
-
JUST in case you need it as a single query (In the absence of CTE's, I do like the temp table solution better which is why I didn't answer previously
)...SET NOCOUNT ON
DECLARE @MyTable TABLE
(
[ID] INT,
[Name] VARCHAR(25)
)
INSERT @MyTable
SELECT 1, 'SomeName1_1' UNION ALL
SELECT 1, 'SomeName1_2' UNION ALL
SELECT 2, 'SomeName2_1' UNION ALL
SELECT 2, 'SomeName2_2' UNION ALL
SELECT 2, 'SomeName2_2' UNION ALL
SELECT 2, 'SomeName2_2' UNION ALL
SELECT 2, 'SomeName2_3' UNION ALL
SELECT 3, 'SomeName3_2' UNION ALL
SELECT 3, 'SomeName3_1' UNION ALL
SELECT 3, 'SomeName3_3' UNION ALL
SELECT 4, 'SomeName4_2' UNION ALL
SELECT 4, 'SomeName4_3' UNION ALL
SELECT 4, 'SomeName4_3' UNION ALL
SELECT 5, 'SomeName5_1' UNION ALL
SELECT 6, 'SomeName6_2' UNION ALL
SELECT 6, 'SomeName6_3'
SELECT c1.ID,MIN(c1.Name) AS Name,c1.NameCount
FROM (
SELECT ID,Name,COUNT(*) AS NameCount
FROM @MyTable
GROUP BY ID,Name
)c1
,
(
SELECT m1.ID, MAX(m1.NameCount) AS MaxNameCount
FROM (SELECT ID,Name,COUNT(*) AS NameCount
FROM @MyTable
GROUP BY ID,Name
)m1
GROUP BY m1.ID
)m2
WHERE c1.ID = m2.ID
AND c1.NameCount = m2.MaxNameCount
GROUP BY c1.ID,c1.NameCount
ORDER BY c1.ID,c1.NameCount
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply