combination_trump_part_aggr.sql

SELECT number1, number2, COUNT(1) AS cnt
FROM
(
  SELECT n1.symbol,n2.symbol, n1.number AS number1, n2.number AS number2
  FROM
  (
    SELECT number, symbol
    FROM trump_part
    WHERE symbol!='joker'
  ) n1
  JOIN
  (
    SELECT number, symbol
    FROM trump_part
    WHERE symbol!='joker'
  ) n2
  ON n1.symbol = n2.symbol
  HAVING n1.number < n2.number
) tmp
GROUP BY number1, number2
ORDER BY cnt DESC, number1, number2