How can I display the most common value sequence in MySQL?

Question

Lets say I have a table like this.

A | B
-----
1 | 4
7 | 8
1 | 3
1 | 2
4 | 5
3 | 5
2 | 5
7 | 8

Even though 1 is the most common value in column A and 5 is the most common value in column B (which can be checked pretty simply with a COUNT()) the result I am looking for is the pair <7, 8> since that pair appears twice.

So how can I get the most common value sequence from a table, since I have n columns that I need to do this with.

Are you saying that you're looking for the statistical *mode* of the tuple (A,B)? In other words the combination of A,B which appears most frequently? — Jim Dennis, May 16 '11 at 17:41

score 10 · Accepted Answer · edited Aug 04 '11 at 19:40

10

select A, B, count(*) as freq
from MyTable
group by A, B
order by count(*) desc
limit 1

edited Aug 04 '11 at 19:40

Brad Mace

27,194
17
102
148

answered May 16 '11 at 17:35

D'Arcy Rittich

167,292
40
290
283

How can I display the most common value sequence in MySQL?

1 Answers1