In SQL in a "group by" expression: how to get the string that occurs most often in a group?

Question

Assume we have the following table:

Id   A    B
1    10   ABC
2    10   ABC
3    10   FFF
4    20   HHH

As result of a "group by A" expression I want to have the value of the B-Column that occurs most often:

select A, mostoften(B) from table group by A;

A    mostoften(B)
10   ABC
20   HHH

How do I achieve this in Oracle 10g?

Remark: in the case of a tie (when there are more than one value that occurs most often) it does not matter which value is selected.

Answer 1

select A, B
from (
  select A, B, ROW_NUMBER() OVER (PARTITION BY A ORDER BY C_B DESC) as rn
  from (
     select A, COUNT (B) as C_B, B
     from table
     group by A, B
  ) count_table
) order_table
where rn = 1;

You want the Bs with the MAX of COUNT group by A, B.

Answer 2

try this (works on SQL Server 2005):

declare @yourtable table (rowid int, a int,b char(3))
insert into @yourtable values (1,10,'ABC')
insert into @yourtable values (2,10,'ABC')
insert into @yourtable values (3,10,'FFF')
insert into @yourtable values (4,20,'HHH')

;WITH YourTableCTE AS
(
SELECT
    *, ROW_NUMBER() OVER(partition by A ORDER BY A ASC,CountOfB DESC) AS RowRank
    FROM (SELECT 
              A, B, COUNT(B) AS CountOfB
              FROM @yourtable
              GROUP BY A,B
          ) dt
)
SELECT
    A,B
    FROM YourTableCTE
    WHERE RowRank=1

EDIT without CTE...

SELECT
    A,B
    FROM (SELECT
              *, ROW_NUMBER() OVER(partition by A ORDER BY A ASC,CountOfB DESC) AS RowRank
              FROM (SELECT 
                        A, B, COUNT(B) AS CountOfB
                        FROM @yourtable
                        GROUP BY A,B
                    ) dt
         ) dt2



    WHERE RowRank=1

Answer 3

This problem can be clarified by creating a view for the count in each A & B group:

CREATE VIEW MyTableCounts AS
  SELECT A, B, COUNT(*) C
  FROM MyTable
  GROUP BY A, B;

Now we can do a query that finds the row c1 where the count is greatest. That is, no other row that has the same A has a greater count. Therefore if we try to find a row c2 with a greater count, no match is found.

SELECT c1.A, c1.B
FROM MyTableCounts c1
LEFT OUTER JOIN MyTableCounts c2
  ON (c1.A = c2.A AND (c1.C < c2.C OR (c1.C = c2.C AND c1.B < c2.B)))
WHERE c2.A IS NULL
ORDER BY c1.A;

To resolve tied counts (c1.C = c2.C), we use the value of B which we know is unique within a given group of A.

Answer 4

Old school solution, it took me some time and some cursing :)

select a,b
from   ta ta1
group by a,b
having count(*) = (select max(count(*))
                   from   ta ta2
                   where  ta1.a = ta2.a
                   group by b)