mysql group by query with column filter

Question

I have a mysql table having data mentioned below with column C1, C2 and C3. Table data is mentioned below(dummy data since confidential). I want a mysql query which will return count of all the C1 column order by c1 descending(to get maximum count C1 first) which has maximum count desc and C2 data of C1 having repeated maximum times and same with C3.

    C1  C2  C3
    X   A   U
    X   A   U
    X   B   V
    Y   H   K
    Y   H   K
    Y   H   K
    Z   F   R
    Z   F   P
    Z   G   P
    Z   F   R

Output should be in below format.

  Count  C1   C2  C3
  4      Z    F   P
  3      Y    H   K
  3      X    A   U

I am able to get the count but not the max repeated value of C2, C3 in the same query.

Answer 1

You can try to use select subquery to make it.

Query 1:

SELECT 
       COUNT(*),
       C1,
       (SELECT tt.C2 FROM T tt WHERE t1.C1 = tt.C1 GROUP BY tt.C2 ORDER BY COUNT(*) DESC,tt.C2 LIMIT 1) c2,
       (SELECT tt.C3 FROM T tt WHERE t1.C1 = tt.C1 GROUP BY tt.C3 ORDER BY COUNT(*) DESC,tt.C3 LIMIT 1) c3
FROM T t1
GROUP BY C1 
ORDER BY COUNT(*) desc,C1 desc

Results:

| COUNT(*) | C1 | c2 | c3 |
|----------|----|----|----|
|        4 |  Z |  F |  P |
|        3 |  Y |  H |  K |
|        3 |  X |  A |  U |